BookmarkSubscribeRSS Feed
KC_16
Fluorite | Level 6

Hi, 

 

I have 22 variables named as below within my proc sql statement:

 

proc sql;

create table work.testing as 

select 

F1V, F1S, F2V, F2S, F3V, F3S, F4V, F4S, F5V, F5S, F6V, F6S, F7V, F7S, F8V, F8S, F9V, F9S, F10V, F10S, F11V, F11S

from work.new;

quit;

 

All the variables have a number which represents a month and I would like to create a new "Date" variable by using the existing 22 variables as above. 

 

So for the variables below  

F1V

F1S

I want the new variable "date" to show current month "Feb"

 

The next two variables 

F2V 

F2S

I want the new variable "Date" to show next month "March"

 

......and so on until the last variables have a 12 within the new "Date" Variable. I hope that makes sense. 

 

Any idea if this is possible and if so, how do I go about writing this out please? 

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Perhaps review any of the posts on this kind of subject.  Transposed data and "excel" way of thinking is making you fight both SAS, and SQL.  Apply some simple data modelling changes and your question will answer itself.  So let me present it both ways, you could go down the route of something like:

data want;
  set have;
  array v{11} f1v f2v f3v...;
  array s{11} f1s...;
  array dates{11} 8;
  do i=1 to 11;
    select(vname(v{i});
      when...
    end;
  end;
run;

Something like that would work (and you have provided no output required, nor test data to work with so just guessing).  However if you carry on like that you can hit all kinds of issues, like typing all those variables out each, what happens when you go over a year, what happens when you want to do calculations etc.  It just really isn't a good way of handling your data.  Now lets look at it from a non-Excel way of thinking:

INDEX  F_SECTION  F_VALUE  

1           V                    ...

1           S                    ...

...

 

This is then simply a matter of applying a format to index to display it as a month.  It is extendable infinitely, without having to recode any variables, and if you make index a proper date variable rather than just parts of one (remember you can still format it any way you like) then you can do date calculations, exctract data between dates etc.  None of which you can do (at least easily) with your current modelling.

KC_16
Fluorite | Level 6

Could I have a macro like below (but up to 11) and then write a case statement?

 

%let currmonth= %sysfunc(month("&sysdate"d));
%let plusone= %sysfunc(month,+1("&sysdate"d));
%let plustwo= %sysfunc(month,+2("&sysdate"d));

 

case

when F1V and F1S = &currmonth. then "Feb"

when F2V and F2S = &plusone. then "Mar" 

when F3V and F3S = &plustwo. then "Apr" 

etc

RW9
Diamond | Level 26 RW9
Diamond | Level 26

You could, anything is possible, you could paint your code onto paper and scan it in if you like.  Question is why.  Bad data modelling is going to affect every single line of code you write from there on in, if you think thats worth it...

 

This doesn't work by the way:

%let currmonth= %sysfunc(month("&sysdate"d));
%let plusone= %sysfunc(month,+1("&sysdate"d));
%let plustwo= %sysfunc(month,+2("&sysdate"d));

 

Maybe something like:

%let currmonth= %sysfunc(month("&sysdate"d));
%let plusone= %sysfunc(month(%sysfunc(intnx(month,"&sysdate"d,1))));
%let plustwo= %sysfunc(month(%sysfunc(intnx(month,"&sysdate"d,2))));

 

And so the mess of coding which is unreadable and will fall over every other run carries on from there, for no benefit other than to work in a way against the software.

KC_16
Fluorite | Level 6

Thank you. 

Just a question on the Macro, I have used the macro below and the output I get is a value of 1, any idea why as I would have expected to see a value of 12?

 

%let Dec = %sysfunc(month(%sysfunc(intnx(month,"&sysdate"d,11))));

KC_16
Fluorite | Level 6
When I add 10 it gives me 11 on the output. So it goes from 11 to 1 and seems to skip 12.

%let Nov = %sysfunc(month(%sysfunc(intnx(month,"&sysdate"d,11))));
Kurt_Bremser
Super User

@KC_16 wrote:
When I add 10 it gives me 11 on the output. So it goes from 11 to 1 and seems to skip 12.

%let Nov = %sysfunc(month(%sysfunc(intnx(month,"&sysdate"d,11))));

WRONG.

See this log (run today, 2018-03-01):

24         %put %sysfunc(month(%sysfunc(intnx(month,"&sysdate"d,8))));
11
25         %put %sysfunc(month(%sysfunc(intnx(month,"&sysdate"d,9))));
12
26         %put %sysfunc(month(%sysfunc(intnx(month,"&sysdate"d,10))));
1
27         %put %sysfunc(month(%sysfunc(intnx(month,"&sysdate"d,11))));
2

You can clearly see that it does NOT skip December in ANY way.

Reeza
Super User

1. that code isn't correct - before you make a macro make sure the code is valid.

2. Avoid macros unless absolutely required

3. You're essentially hardcoding the relationship into your code and that's a really bad way to design this. If you ever need to revisit this program you're going to have fun. 

4. Transpose - a long format is significantly better for analysis. This is consistent across all fields, and if you want to research it, look up 'Tidy Data' by Hadley Wickham.

 


@KC_16 wrote:

Could I have a macro like below (but up to 11) and then write a case statement?

 

%let currmonth= %sysfunc(month("&sysdate"d));
%let plusone= %sysfunc(month,+1("&sysdate"d));
%let plustwo= %sysfunc(month,+2("&sysdate"d));

 

case

when F1V and F1S = &currmonth. then "Feb"

when F2V and F2S = &plusone. then "Mar" 

when F3V and F3S = &plustwo. then "Apr" 

etc


 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 1023 views
  • 0 likes
  • 4 in conversation