CODEWITHSUNDEEP
sassas-macro
rom
rom

Reputation: 81

SAS - Change formats for multiple variables in all datasets

I am writing a SAS macro that is trying to check and change formats, and also check and report on exclusions (values that should not be there), valid values (should have only XYZ), etc. for all variables and all data sets in a library.

The code below is for changing formats only.

I have created a reference file which is a combination of the output from proc contents and an external file that has the rules for each variable. So this file has data set names, variables names, and all the rules (formats, exclusions, valid values, min, max, etc.)

I am not able to make SAS go through each variable in each data set - the last section of this code is where I am getting stuck.

options mprint;
%macro reformat_var;

data _null_;
set SASDATA.Reference_File;
if Country='USA' then do;
    call symput("SASData",SASData_Folder);
    call symput("Country_Name",Country);
end;
run;

proc sql noprint;
select Data_Name, /*SAS dataset name */
       Var_Name,  /* Variable name */
       Column_informat, /* current format of the variable in the data */
       Column_format  /* format I want to change to */
into : Data_List separated by ' ',
     : Var_List separated by ' ',
     : Informat_List separated by ' ',
     : Format_List separated by ' '
from SASDATA.Reference_File;
quit;

proc sql noprint;
select count(filen) 
into :cntfile 
from sasdata._indexfile; 
/*created in a prior step, indexfile has a list of all the SAS dataset names */
%if &cntfile>=1 %then %do;
    select filen into :filen1-:filen%left(&cntfile)
    from sasdata._indexfile;
%end;
quit;

/* Change date formats  - this is where I am getting stuck – how do I check format 
for each variable in each dataset using the macro variables (Data_List, Var_List, 
Informat_list, Format_List) */

%do i=1 %to &cntfile;
    data sasdata.&&filen&i;
    set sasdata.&&filen&i;
    %if &Country_Name='USA' and &Informat_list='mmddyy10.' %then %do; 
        format &Var_List &Format_list;
    %end;
%end;
run;
%mend reformat_var;

%reformat_var;

Upvotes: 1

Views: 3357

Answers (1)

Reeza
Reeza

Reputation: 21264

I don't recommend using this methodology.

Instead consider using the SASHELP.VCOLUMN Dataset to generate a list of the variables and formats as well. Then apply the new formats you want using a proc datasets, this way you're not reading in/out the entire dataset.

This way you can compare the two files fairly easily because they'll have the same columns/rows hopefully :)

If you want to continue in this way I'd suggest changing your PROC SQL step to read something like

proc sql;
select "format" || var_name || " " || format_name 
    into :format_list separated by ";"
from SASDATA.Reference_File;
quit;

%put &format_list;

This would generate a format statement as follows:

 format var1 format1;
 format var2 format2;
 format var3 format3;

You can do the same for informats as well.

Hope that helps to get you started.

Upvotes: 1

Related Questions