Desktop productivity for business analysts and programmers

creating a datasubset based on the weeknumber of the year

Accepted Solution Solved
Reply
Regular Contributor
Posts: 168
Accepted Solution

creating a datasubset based on the weeknumber of the year

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

 

 


Accepted Solutions
Solution
‎01-11-2018 01:23 PM
Super User
Posts: 6,934

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

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.

 

View solution in original post


All Replies
Super User
Posts: 6,934

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

[ Edited ]

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.

Super User
Super User
Posts: 9,840

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

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!

Super User
Posts: 10,571

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

Concur with others, transpose and use where condition.

See Maxim 19.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 288

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

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

Super User
Posts: 13,941

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

Posted in reply to AhmedAl_Attar

@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.

Regular Contributor
Posts: 168

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

Posted in reply to AhmedAl_Attar

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

 

 

 

Solution
‎01-11-2018 01:23 PM
Super User
Posts: 6,934

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

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 296 views
  • 6 likes
  • 6 in conversation