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
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?
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?
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.
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.