topic Re: creating a datasubset based on the weeknumber of the year in SAS Enterprise Guide

creating a datasubset based on the weeknumber of the year

alepage — Thu, 11 Jan 2018 15:32:05 GMT

Hello,

I have a dataset let's say Temp1 with the following variables (10 observations):

prov P1-5 P6_10 P16_20 P21_25 P26_30 P31_35 P36_40 P41_45 P46_50 P51_52

Based on the weeknumber of the year I would like to select a specific variable.

for example, if weeknumber =47 then I would like to create a datasubset with prov and P46_50

So, what I have done is to create a macro variable name VarName using the following macro function

%let week2=35;

%macro SelectVar;
%global VarName week2;
%if &week2. ge 1 and le 5 %then %let VarName=P1_5;
%if &week2. ge 6 and le 10 %then %let VarName=P6_10;
%if &week2. ge 11 and le 15 %then %let VarName=P11_15;
%if &week2. ge 16 and le 20 %then %let VarName=P16_20;
%if &week2. ge 21 and le 25 %then %let VarName=P21_25;
%if &week2. ge 26 and le 30 %then %let VarName=P26_30;
%if &week2. ge 31 and le 35 %then %let VarName=P31_35;
%if &week2. ge 36 and le 40 %then %let VarName=P36_40;
%if &week2. ge 41 and le 45 %then %let VarName=P41_45;
%if &week2. ge 46 and le 45 %then %let VarName=P46_50;
%if &week2. ge 51 and le 52 %then %let VarName=P51_52;

%mend SelectVar;
%SelectVar;
%put &Varname;

Then I can create my datasubset

Data Temp2 (keep=prov &Varname);

set Temp1 (firstobs=1 obs=4);

run;

Is there a more efficient way to do that?

Thanks for your help

Alain

Re: creating a datasubset based on the weeknumber of the year

Astounding — Thu, 11 Jan 2018 15:43:05 GMT

First, the syntax needs to be cleaned up. You have to repeat the first side of the comparison:

%if &week2. ge 6 and &week2. le 10 %then %let VarName=P6_10;

You can speed up the macro logic by adding %ELSE, but the difference will be so small that you can't measure it. More important, you will gain speed by switching KEEP= to the SET statement:

Data Temp2;

set Temp1 (firstobs=1 obs=4 keep=prov &Varname);

run;

That way, the program reads in only the variables that are needed.

Re: creating a datasubset based on the weeknumber of the year

RW9 — Thu, 11 Jan 2018 15:47:58 GMT

Yes, pretty sure its been mentioned before. Transposed datasets are difficult to work with from a programming point of view. There also is no benefit to doing it in such a way as you can simply transpose a final dataset to get required output. Normalise your data:

proc transpose data=have out=want;
  by prov;
  var p1--p51_52;
run;

Then you can process the _name_ column to be a bit more usable, and simply do where clauses on the data based on weeknumber. This way is far simpler than the masses of macro code you are going to write simply to maintain a normalise data structure through your programming!

Re: creating a datasubset based on the weeknumber of the year

Kurt_Bremser — Thu, 11 Jan 2018 15:53:42 GMT

Concur with others, transpose and use where condition.

See Maxim 19.

Re: creating a datasubset based on the weeknumber of the year

AhmedAl_Attar — Thu, 11 Jan 2018 16:04:52 GMT

Hi,

I would suggest this approach

/* Create a Format mapping Week Number to a Variable Name */
proc format ;
    value wkVarName
    1-5='P1_5'
    6-10='P6_10'
 11-15='P11_15'
 16-20='P16_20'
 21-25='P21_25'
 26-30='P26_30'
 31-35='P31_35'
 36-40='P36_40'
 41-45='P41_45'
 46-50='P46_50'
 51-52='P51_52';
run;

/* Usage Example */
data have;
 length prov P1_5 P6_10 P16_20 P21_25 P26_30 P31_35 P36_40 P41_45 P46_50 P51_52 8;
 stop;
 run;

 data want;
    set have(keep=prov %sysfunc(putn(%sysfunc(week("&sysdate"d)),wkVarName.)) ); /* Dynamically derive the variable name based on the current week number */
run;

Hope this helps,

Ahmed

Re: creating a datasubset based on the weeknumber of the year

ballardw — Thu, 11 Jan 2018 16:21:52 GMT

@AhmedAl_Attar your approach is similar to my first thoughts as well. But there could well be an underlying problem as mentioned by several posters here: the data is not well structured and could well in a few weeks add the requirement for week=56 or week=64 or <continue ad nauseum>. Which requires continually updating the format. A case could be made for using a data step to make a much larger number of format elements but I would think that masks a poor data structure and only addresses one variable set. Who knows if there are other variables with similar behavior to address.

If the data is restructured then most of the headaches would be much easier to deal with in the longer term.

Re: creating a datasubset based on the weeknumber of the year

alepage — Thu, 11 Jan 2018 16:51:56 GMT

Hello,

I am not very confortable with proc format so excuse me if my question seem very basic.

how do you select the good wkVarname?

for example, suppose that weeknumber is 34 then I would select wkVarName=P31_35.

The rest of your code to select dynamicaly the variable is working as long as you select the good wkvarname at first.

Thanks for help

Alain

Re: creating a datasubset based on the weeknumber of the year

Astounding — Thu, 11 Jan 2018 17:45:02 GMT

It depends on how you supply the week number. In your original example, you do it with:

%let week2=35;

If that's going to be the way you supply a value, you would use:

set have(keep=prov %sysfunc( putn(&week2,wkVarName.) ) ;

The example provided by @AhmedAl_Attar assumes that you want the program to calculate the value for &WEEK2 based on the date, instead of supplying it with a %LET statement.