Do Loop Macro

Reply
Frequent Contributor
Posts: 122

Do Loop Macro


Hi Experts,

 

I have a library with many datasets of same variables. I am tryng to count the number of observations of each dataset by names and place them in a master dataset(result).

 

Below are the datsets which ends with daykey.

 

libname ab "/c:/users/desktop/files";

data_201800107
data_201800114
data_201800121
data_201800128
data_201800207
data_201800214
data_201800221
data_201800228
data_201800302
data_201800309
data_201800316
data_201800323
data_201800320


I can do this with the below code.

 

data result;
if _n_ = 1 then delete;
run;

%macro test;
%do i = 0 %to 87;
data _NULL_;
OFFSET1=&i;
X1=PUT(DAY(INTNX('Day',TODAY(),-(OFFSET1+0),'b')),Z2.);
y1=PUT(MONTH(INTNX('Day',TODAY(),-(OFFSET1+0),'b')),Z2.);
z1=PUT(YEAR(INTNX('Day',TODAY(),-(OFFSET1+0),'b')),$4.);
dayk=z1||y1||x1;
call symput("dayk",dayk);
run;
%put &dayk;

%if %sysfunc(exist(ab.data_&dayk.)) %then %do;

proc sql;
create table new_&dayk. as
select count(*) as count,name, from ab.data_&dayk.
group by name;
quit;

data new_&dayk.;
set new_&dayk.;
rundate = "&dayk.";
run;

data result; /*for master data creation */
set result new_&dayk.;
run;


%end;

%end;


%mend test;

%test;

 

But when  these data sets have month and date how can I do this.

Below are the sample datasets.

 


libname ab "/c:/users/desktop/files";

data_jan_201800107
data_jan_201800114
data_jan_201800121
data_jan_201800128
data_feb_201800207
data_feb_201800214
data_feb_201800221
data_feb_201800228
data_mar_201800302
data_mar_201800309
data_mar_201800316
data_mar_201800323
data_mar_201800320

 

Thanks & Regards

Sanjay

 

 

Super User
Super User
Posts: 9,840

Re: Do Loop Macro

[ Edited ]

You do not need to do any of that:

data want;
  set sashelp.vtable (where=(libname="AB") keep=libname memname nobs);
run;

Two tips, if you start creating lots of macro to process your data then consider that you have modelled your data in a less than optimal way.  Putting data in dataset names is not a good idea, its makes all your programming effort a lot more difficult and less robust.  Also takes more space.  

 

Oh, I just noticed you want by groups not overall, so change to:

data want;
  length dataset $200;
  set ab.data_: indsname=tmp;
  dataset=tmp;
run;

proc sql;
  create table want as
  select dataset,
         name,
         place,
         count(*) as tot
   from  want
   group by dataset,
            name,
            place;
quit;

You will note that I put all the data together - and you will see how much simpler this makes coding.

 

Also note that the syntax:

libname ab "/c:/users/desktop/files";

Is invalid, are you working on Windows, or UE version or something?

Frequent Contributor
Posts: 122

Re: Do Loop Macro

I am working on windows.

 

How can I check one particualr dataset volume from want dataset just to make sure that its reading right.

 

Example : From want dataset if I want to read only  data_jan_201800114 how can I approach this.

Super User
Super User
Posts: 9,840

Re: Do Loop Macro

In my code, you can filter the dataset with a where clause, or just look at the sumarised data or if its just one dataset you want to look at rather than using prefix:, just put the dataset name e.g:

data want;
  length dataset $200;
  set ab.data_jan_201800114 indsname=tmp;
  dataset=tmp;
run;

proc sql;
  create table want as
  select dataset,
         name,
         place,
         count(*) as tot
   from  want
   group by dataset,
            name,
            place;
quit;
Ask a Question
Discussion stats
  • 3 replies
  • 61 views
  • 0 likes
  • 2 in conversation