topic Re: Create a new Date variable on the back of existing variable in SAS Programming

Create a new Date variable on the back of existing variable

KC_16 — Wed, 28 Feb 2018 10:26:25 GMT

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?

Re: Create a new Date variable on the back of existing variable

RW9 — Wed, 28 Feb 2018 10:34:08 GMT

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.

Re: Create a new Date variable on the back of existing variable

KC_16 — Wed, 28 Feb 2018 11:00:05 GMT

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

Re: Create a new Date variable on the back of existing variable

RW9 — Wed, 28 Feb 2018 11:24:00 GMT

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.

Re: Create a new Date variable on the back of existing variable

KC_16 — Wed, 28 Feb 2018 13:52:03 GMT

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))));

Re: Create a new Date variable on the back of existing variable

Kurt_Bremser — Wed, 28 Feb 2018 13:55:43 GMT

Today is 28feb2018, so adding 11 months to that with intnx will give you 28jan2019, the month() of whch is 1.

Re: Create a new Date variable on the back of existing variable

KC_16 — Wed, 28 Feb 2018 13:58:41 GMT

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))));

Re: Create a new Date variable on the back of existing variable

Reeza — Wed, 28 Feb 2018 16:40:12 GMT

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

Re: Create a new Date variable on the back of existing variable

Kurt_Bremser — Thu, 01 Mar 2018 07:07:03 GMT

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