Hey guys,
so I have Data from year 2005, 2006, 2007, ... up to 2017, called "drg2005_sf" and so on.
And I want my tables (for example how many males and famles are there this year), for each year in the same way. But I want to have 8 single tables in the end so I see how many males/females I have in 2005, how many in 2006, ...
What I have is:
%macro muster;
%let startjahr = 2005;
%let endejahr = 2017;
%do year = &startjahr %to &endejahr;
data drg_&year;
set daten.&datenname.&year._sf
....
data drg;
set drg_&year
%if &year > &startjahr %then %do;
drg
%end;
;
run;
%end;
%mend muster;
%muster
Down below I got:
proc freq data = drg;
title "Output Nr. 1 male/female";
table XX;
run;
In my work libary I get 8 outfiles, but the result from my "proc freq data" is all males/females added up 2005 + 2006 ,... and not seperated.
Hope you get my problem and may help me, thanks a lot!
Here's an example to do what I think you're aiming to do:
data have;
set sashelp.class;
birth_year=2019-age;
run;
proc sql;
create table years as
select distinct birth_year from have
order by birth_year;
quit;
data _null_;
set years;
call execute(cats(
'data prefix_',birth_year,';
set have(where=(birth_year=',birth_year,'));
run;'
));
run;
-unison
I don't quite get what your macro code is doing and likely mostly unneeded.
You will find that in most cases you would be better off using names like DRG_sf_2005 instead of drg2005_sf. That would allow combining the data with code like:
data drg; set drg_sf_2005 - drg_sf_2017; run;
If you don't have any other sets that start with drg2005 you may also be able to use:
data drg; set drg2005: - drg2017: ; run;
Do you have a variable in your data set indicating which year it is from? If not then add one and you would get separate tables for each year by
Proc sort data=drg; by year; run; proc freq data = drg; title "Output Nr. 1 male/female"; by year; table XX; run;
Thank you, too!
Here is the same problem:
data drg;
set drg2005: - drg2017:
;
But the Error:
The data set list (WORK.drg2005:) does not contain any members
The data set list (WORK.drg2017:) does not contain any members
so it does not count up from 2005, 2006, ... to 2017, and it doesn't get my data.
And no, I don't have a variable in your data set indicating which year it is from, and unfortunately I cannot add one because the data itself has to be unchanged .
YOu want to do the same table for the 13 successive years 2005 through 2017. And, luckily the datasets are named DRG_2005, DRG_2006, ... DRG_2017.
You can make a data set from all 13 successive years, and do a single proc FREQ with a BY statement telling SAS to do the table for each of the 13 consecutive by-levels.
Did I say "make a data set"? Yes, but you don't have to worry about excessive disk space use or input/output actrivity, because you can make a data set VIEW, not a data set FILE.
data need /view=need;
set mylib.DRG_20: indsname=indsn;
dsname=indsn;
run;
proc freq data=need;
by dsname;
tables ..... ;
run;
Thanks everyone for your help!
The data set list (WORK.DRG_20:) does not contain any members
The Error is always this - SAS can't find my data.
So the rest afterwards doesn't work either.
But thank you for the explanation of your Codes, it really helps. But I don't know what's the problem now. And are the ":" always that the rest of the name doesn't matter? Like in my case, it's the beginning DRG_20 and whatever comes after will be worked with?
Hi Hannah,
Try running this (an amendment to my code above). I think you can adapt it to directly work with your problem.
*sets a prefix for your tables;
%let pfx = drg_tbls;
/*Combine your source tables -- if you have a table that is all of these combined, use that.
(here, I'm assuming data_year is a variable in each set
otherwise I'd write a line to create that variable.)*/
data have;
set sasuser.drg20: ;
run;
*get distinct data_years from your have source dataset - store in dataset called years;
proc sql;
create table years as
select distinct data_year from have order by data_year;
quit;
*loop through 'years' dataset and for each data_year, call excute code that has the template form:;
/* data &pfx._data_year; */
/* set have(where=(data_year=data_year)); */
/* run; */
data _null_;
set years;
call execute(cats(
"data &pfx._",data_year,';
set have(where=(data_year=',data_year,'));
run;'
));
run;
*Just a check to see what data is in work library;
proc datasets lib=work memtype=data;
run;
*Combines all datasets in work with the prefix you chose. -- sets dsname to the dataset name.;
data combine;
set &pfx.: indsname=dataset;
dsname=dataset;
run;
*Execute proc freq on combine show tables by dsname and sex.;
proc freq
data=combine;
tables dsname*sex;
run;
It might help to use proc datasets to see what's in the WORK library.
-unison
Hey, thank you.
Because I don't know what every step in your code means, I don't know exactly what to change for my specific data names etc.
What I used:
data drg;
set sasuser.drg20:;
data_year=2005+1;
run;
proc sql;
create table years as
select distinct data_year from have order by data_year;
quit;
data _null_;
set years;
call execute(cats(
"data &pfx._",data_year,';
set have(where=(data_year=',data_year,'));
run;'
));
proc datasets lib=work memtype=data;
run;
data combine;
set &pfx.: indsname=dataset;
dsname=dataset;
run;
proc freq
data=combine;
tables dsname*sex;
run;
As output I get:
work.drg
work.combine
work.years
work.praefix2006
But in my work.drg table there are not all variables, so in the steps below sas always says "Variable xy is not in work.drg"
I don't get why it is so difficult
Hi Hannah, thanks for posting your code. I went through my above post and commented what each step does. I think one of the main problems is that your source table 'drg' shares the prefix of your year tables (i.e. drg_2005). Also, it looks like you defined data_year=2005+1 which is why you're only seeing 2006 as an output. If data_year is in your drg20XX_sf datasets then you won't have to define it at all. Try my edited version and make adjustments as needed. I hope it's a little more clear now. Let me know if you have any questions!
-unison
Since your data is already divided into data_years, this would be a quicker way to do this:
*sets a prefix for your tables;
%let pfx = drg20;
*Combines all datasets in work with the prefix you chose. -- sets dsname to the dataset name.;
data combine;
set sasuser.&pfx.: indsname=dataset;
dsname=dataset;
run;
*Execute proc freq on combine show tables by dsname and sex.;
proc freq
data=combine;
tables dsname*sex;
run;
-unison
Thank you for your edited Text, it helped to understand what everything stands for.
When I use the Codes from your last post:
I get one output, namend Combine
And so many errors that I don't even know where to start.
When I use a different code:
%macro muster;
%do year = 2005 %to 2017;
data drg_&year;
set daten.&datenname.&year
.
.
.
ods html close;
proc datasets library=work nolist;
delete drg_&year;
run;
%end;
%mend muster;
%muster
It runs threw the years, but says:
Data DATEN.DRG2005.DATA doesn't exist
For every year
Well that's right, because the data is namend drg2005_sf
But when I change to:
data drg_&year;
set daten.&datenname.&year_sf
Then it says:
ERROR: Data DATEN.DRG.DATA doesn't exist
ERROR: Undeclared array referenced: year_sf
Aren’t those datasets in your ‘sasuser’ library? As opposed to this ‘daten’ library.
Change '&year_sf' to '&year._sf'
My pleasure.
In this case your macro variable was called 'year', so you need to tell SAS where your macro variable ends when you type 'year' followed by '_sf'. The way to do that is with the '.' -- otherwise, SAS thinks your macro variable is called 'year_sf'
-unison
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.