topic Re: Data over many years in SAS Programming

Data over many years

hannah_hortlik — Thu, 14 Nov 2019 18:44:17 GMT

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!

Re: Data over many years

unison — Thu, 14 Nov 2019 19:01:27 GMT

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

Re: Data over many years

ballardw — Thu, 14 Nov 2019 19:03:02 GMT

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;

Re: Data over many years

mkeintz — Thu, 14 Nov 2019 20:03:14 GMT

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;

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.
So the dataset name is saved in permament variable DSNAME.
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.

Re: Data over many years

hannah_hortlik — Sun, 17 Nov 2019 18:28:29 GMT

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?

Re: Data over many years

hannah_hortlik — Sun, 17 Nov 2019 18:36:11 GMT

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 .

Re: Data over many years

unison — Mon, 18 Nov 2019 15:16:03 GMT

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

Re: Data over many years

hannah_hortlik — Mon, 18 Nov 2019 08:34:26 GMT

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

Re: Data over many years

unison — Mon, 18 Nov 2019 15:18:36 GMT

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

Re: Data over many years

unison — Mon, 18 Nov 2019 15:22:26 GMT

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

Re: Data over many years

hannah_hortlik — Tue, 19 Nov 2019 11:19:35 GMT

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

Re: Data over many years

unison — Tue, 19 Nov 2019 14:14:55 GMT

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

Re: Data over many years

hannah_hortlik — Wed, 20 Nov 2019 14:52:10 GMT

The datasets are in both. And even if I change That it it doesn‘t matter

Re: Data over many years

unison — Wed, 20 Nov 2019 15:26:34 GMT

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

Re: Data over many years

hannah_hortlik — Wed, 20 Nov 2019 17:21:40 GMT

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.

Re: Data over many years

unison — Thu, 21 Nov 2019 02:26:33 GMT

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