0
$\begingroup$

I have code for which this works perfectly, see code 1 below.

But for code 2, it does not work at all. Very strange. Only difference is the way the tables were created, and the use of macro variables in code 2.

So it's just this part that is relevant in both pieces of code:

 if column1 = 'string 1b' then 
            column2 = 888;

and

 if Petit_Produit = 'AGIS FIS' then 
        Reserve = &temp_sum_reserves;

For completeness, the whole code: Code 1:

data my_dataset;
    do i = 1 to 10;
        /* Randomly select either "string1" or "string2" for column 1 */
        if rand('uniform') < 0.5 then
            column1 = "string 1b";
        else
            column1 = "string 2a";
        
        /* Randomly select either 100 or 200 for column 2 */
        if rand('uniform') < 0.5 then
            column2 = 100;
        else
            column2 = 200;
        
        output;
    end;
    drop i; /* Drop the temporary variable */
run;

/* Output the created dataset */
proc print data=my_dataset;
run;

data my_dataset; * dit werkt soms perfect, soms helemaal niet, super vreemd;
    set my_dataset; /* Use the existing dataset as input */
    /* Check if column 1 is 3, then replace column 2 value with 666 */
    if column1 = 'string 2a' then
        column2 = 777;
run;


data my_dataset; * dit werkt soms perfect, soms helemaal niet, super vreemd;
    set my_dataset; /* Use the existing dataset as input */
    /* Check if column 1 is 3, then replace column 2 value with 666 */
    if column1 = 'string 1b' then 
        column2 = 888;
run;


/* Output the created dataset */
proc print data=my_dataset;
run;

Code 2:

%let SkipImportData = 1; *not used;

/* Assigning values to non-numeric variables */
%let directory = ...\input_files_copy\01_2024; /* Example directory */
%let filename = CONSOPL_J9.xlsx; /* Example filename */

/* Concatenating directory and filename with enclosing double quotes */
%let input_path = &directory.\&filename;

/* Display the value of input_path */
%put Input path: &input_path;

/*createa library to store imported files in */
LIBNAME PetitsPr 'S:\Ruben_stuff\SasTests\SasTestLibrary';

*Create the structure for the output file (maybe better if this is read from excel, so the user can adapt the names... but ...);
data my_dataset;
    length Petit_Produit $30; /* Define length for the character variable */
    input Petit_Produit $ Reserve comma12.2; /* Define Reserve as numeric variable */
    format Reserve comma12.2; /* Define format for Reserve */
    datalines;
PROTECTION EMPLOI INV, 
AGIS FIS, 
AGIS INV, 
AGIS RSK, 
LEO FIS, 
LEO INV, 
EIP INV, 
PAW BANK, 
PAW BROKER, 
PROTECTION FIS, 
;
run;

/* Importing Excel file */ *Add Guessing rows;
proc import 
    datafile = "&input_path"
    out = PetitsPr.mydataFULL
    dbms = xlsx
    replace;
    /*sheet = "Sheet1"; /* Specify the sheet name if applicable */
run;

/* Display information about dataset PetitsPr.dataFULL_MOD */
proc contents data=PetitsPr.dataFULL_MOD varnum;
run;

/* Filter data and calculate sum v5*/
data PetitsPr.TempReservesFiltered;
    set PetitsPr.mydataFULL;
    where ITEM_CRT = '1712' and (Product_CRT = '13120_F' or Product_CRT = '13130');
    Total_Reserves = Reality_02_2024 + Closing_02_2024; *Total_Reserves = sum(Reality_01_2024, Closing_01_2024);
run;

* Filter data;
data PetitsPr.TempReservesFiltered2(keep=ITEM_CRT Product_CRT Product_GROUP Reality_02_2024 Closing_02_2024 Total_Reserves); *variable names will change each month, so ... program will need to be able to handle this;
    set PetitsPr.TempReservesFiltered;   
run;



/* Calculate the sum of Total_Reserves */
proc sql noprint;
    select sum(Total_Reserves) into :temp_sum_reserves
    from PetitsPr.TempReservesFiltered2;
quit;

*check calculation;
%put Sum of Total_Reserves: &temp_sum_reserves;



/* Update the copied dataset with the calculated value: method 2 */
data Petitspr.Output_dataset;
    set Petitspr.Output_dataset;
    if Petit_Produit = 'AGIS FIS' then 
        Reserve = &temp_sum_reserves;
run;

/* Output the updated dataset */
proc print data=Petitspr.Output_dataset;
run;
$\endgroup$

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.