Data over many years

[hannah_hortlik]

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!

[unison]

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

[ballardw]

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;

[hannah_hortlik]

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 .

[mkeintz]

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;

1. The SET statement does this:
   1. Reads in all the datasets in libname mylib, whose data set name begins with DRG_20.  Those data sets are read in lexicographic order (i.e. DRG_2005, DRG_2006, ... DRG_2017).
   2. The "indsname=" option stores the name of the data set for each incoming observation into temporary variable INDSN.
2. So the dataset name is saved in permament variable DSNAME.
3. The view is just a definition.  It is not actuated until the view is named in a later proc.  (i.e. the "data=need" in the proc freq statement).  So the data are read in from each and every original data set into the proc freq.  No muss, no fuss.

[hannah_hortlik]

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?

[unison]

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

[hannah_hortlik]

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

[unison]

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

[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

[hannah_hortlik]

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

[unison]

Aren’t those datasets in your ‘sasuser’ library? As opposed to this ‘daten’ library.

[unison · Accepted solution]

Change '&year_sf' to '&year._sf'

[hannah_hortlik]

You made my day. It works - the way I want it and the way it should work, finally. Thank you so much for your help and time! Just because of one ".", crazy. I have to get used to this. You don't know how thankful I am, have a wonderful day.

[unison]

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