SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Snum
Calcite | Level 5

Hello Everyone,

I want to create a programme that will run through all the datasets in a library and then use the proc freq to see the missing values in each datasets. Then export it to my local drive.

 

 

I have a code and would like to modify it a bit. I want to change the red highlighted text with the green highlighted ones. so that I can run the proc freq. Now the problem is I do not know how the create a macro for each datasets (like gphe.MHPRESP1001, it should run for all the 73 datasets in the library)

 

 

 

%let var1=gphl;
%let var2=gphe;


%macro xyz;

%do i=1 %to 2;

libname &&var&i. "/abc_&&var&i./prelock/data/raw/shared" access=readonly;

data &&var&i.._test;
set &&var&i...MHPRESP1001;
where MHDXDAT   MHOCCUR   MHSEV   MHSPID   MHTERM   MHSTDAT   MHONGO   MHENDAT   MHCAT_4   MHPRESP   MHLLTCDPRESP   DICT   DICTVER eq '';
run;
%end;

%mend;
%xyz;
ods output nlevels= MHPRESP1001;
proc freq data= gphe.MHPRESP1001 nlevels;
tables _all_ ;
run;
ods select all;

Then my plan is to export all the datasets from WORK lib to my local folder by using below

libname outfile xlsx "&path.\Forecast.xlsx";

proc copy in=work out=outfile;
run;

libname outfile;

Please can anyone help ?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

You are working way too hard to get the list of datasets.

proc sql noprint;
select distinct memname into :dataset1 -
  from dictionary.tables
  where libname=%upcase("&lib") and memtype='DATA'
;
%let cnt=&sqlobs;
quit; 

That type of code generation is much easier without macro code.  Just use a data step to generate the code directly from the source data without first transferring the data into macro variables.

 

It also might be easier to just build on single dataset that has the NLEVELS data for ALL of the datasets.

So first get the list of members and use that to generate the code to summarize each one and append the results to the summary dataset.  Since you will want to include information about the dataset that the variable live in you could use MODIFY statement in a data step to append instead of using PROC APPEND. Then it is easy to add the dataset name.

 

So here is code to get the dataset list and use it to generate the code to call PROC FREQ and append the results.

proc contents data=&lib.._all_ noprint out=contents;
run;
filename code temp;
data _null_;
  set contents;
  by memname;
  if first.memname;
  file code;
  put 'ods output nlevels=nlevels;';
  put 'proc freq data=' libname +(-1) '.' memname 'nlevels;tables _all_/noprint;run;' ;
  put 'data summary;'
    / 'if _n_=0 then modify summary;'
    / 'set nlevels;'
    / libname = :$quote. ';'
    / memname = :$quote. ';'
    / 'output;'
    / 'run;'
  ;
run;

Now setup an empty SUMMARY dataset and then run the generated code.

data summary;
  length libname $8 memname $32 TableVar $32 NLevels NMissLevels NNonMissLevels 8
         TableVarLabel $256
  ;
  stop;
  call missing(of _all_);
run;

ods select none;
%include code / source2;
ods select all;

Now you can pick from that SUMMARY dataset the observations you want.  Do you want those with no non-missing values?

data want;
  set summary ;
  where NNonMissLevels < 1 ;
run;

 

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

The first step in the process of creating a macro is to create WORKING code for two cases of your situation, hard-coded (in this case, two data set names) that does what you want without macros and without macro variables. Emphasis on WORKING. Right now your code doesn't work, and will not run.

So please provide us with WORKING code for your situation, without macros and without macro variables, for two data sets hard coded.

 

Specifically, this line of code doesn't work, and there may be other errors.

where MHDXDAT   MHOCCUR   MHSEV   MHSPID   MHTERM   MHSTDAT   MHONGO   MHENDAT   MHCAT_4   MHPRESP   MHLLTCDPRESP   DICT   DICTVER eq '';

 

--
Paige Miller
Kurt_Bremser
Super User

In your first LIBNAME, you use a typical UNIX path, but in your second LIBNAME, you have a backslash, pointing to a Windows system. If one of those works, the other can't work, depending on the operating system where SAS executes.

  • What kind of SAS installation do you use? Single machine (desktop), or client/server?
  • If the latter, which operating system on the server?
  • Which interface do you use? Enterprise Guide, SAS Studio, or Display Manager (if single machine)?
Snum
Calcite | Level 5

I got it after much struggle and doing research (as I am not a programmer and a new bird in SAS).

 

proc sql; ** to get the count of datasets in a Lib**;
create table  My as
select distinct memname, libname from dictionary.tables where Libname="&lib" and memtype='DATA';

select distinct count(memname) into :cnt from my;
select distinct memname into :dataset1 - :dataset%left(&cnt) from my;
quit; 
**** macro to run thorough all the datasets in a Lib ***;
%macro Abc;
%do i= 1 %to &cnt;
ods output nlevels= &&dataset&i.;
proc freq data= &lib..&&dataset&i. nlevels;
tables _all_ ;
run;
ods select all;
data out.&&dataset&i.; 
set &&dataset&i.;
where NNonMissLevels=0;
run;
%end;
%mend;
%Abc;
Tom
Super User Tom
Super User

You are working way too hard to get the list of datasets.

proc sql noprint;
select distinct memname into :dataset1 -
  from dictionary.tables
  where libname=%upcase("&lib") and memtype='DATA'
;
%let cnt=&sqlobs;
quit; 

That type of code generation is much easier without macro code.  Just use a data step to generate the code directly from the source data without first transferring the data into macro variables.

 

It also might be easier to just build on single dataset that has the NLEVELS data for ALL of the datasets.

So first get the list of members and use that to generate the code to summarize each one and append the results to the summary dataset.  Since you will want to include information about the dataset that the variable live in you could use MODIFY statement in a data step to append instead of using PROC APPEND. Then it is easy to add the dataset name.

 

So here is code to get the dataset list and use it to generate the code to call PROC FREQ and append the results.

proc contents data=&lib.._all_ noprint out=contents;
run;
filename code temp;
data _null_;
  set contents;
  by memname;
  if first.memname;
  file code;
  put 'ods output nlevels=nlevels;';
  put 'proc freq data=' libname +(-1) '.' memname 'nlevels;tables _all_/noprint;run;' ;
  put 'data summary;'
    / 'if _n_=0 then modify summary;'
    / 'set nlevels;'
    / libname = :$quote. ';'
    / memname = :$quote. ';'
    / 'output;'
    / 'run;'
  ;
run;

Now setup an empty SUMMARY dataset and then run the generated code.

data summary;
  length libname $8 memname $32 TableVar $32 NLevels NMissLevels NNonMissLevels 8
         TableVarLabel $256
  ;
  stop;
  call missing(of _all_);
run;

ods select none;
%include code / source2;
ods select all;

Now you can pick from that SUMMARY dataset the observations you want.  Do you want those with no non-missing values?

data want;
  set summary ;
  where NNonMissLevels < 1 ;
run;

 

Snum
Calcite | Level 5
Thanks a lot TOM for looking into my code :). Your code worked . and Thanks a lot for the detailed explanation and wish you a happy new year .

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 877 views
  • 0 likes
  • 4 in conversation