BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
alepage
Barite | Level 11

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

7 REPLIES 7
Astounding
PROC Star

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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!

AhmedAl_Attar
Rhodochrosite | Level 12

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

ballardw
Super User

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

alepage
Barite | Level 11

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

 

 

 

Astounding
PROC Star

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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