Creating multiple variable groups from arrays

Solved
Occasional Contributor
Posts: 7

Creating multiple variable groups from arrays

Hey there,

I am dealing with historical data that is kept by month. However, to process it correctly I need it on an annual basis with variables representing the month intervals. So for example right now there is a variable called VAR with an obs for each month, which needs to become CDD1-CDD12. Since this needs to happen for a bunch of variables, hard coding is out of the question. My initial thoughts brought me to arrays, seemed like a textbook application for them. With the approach I took, I ran into two problems.

1. The arrays overwrite each other and only the last one is kept.

2. This approach can only handle variables of the same length in name.

```%MACRO LONG2WIDE ;
%LET VARS=CDD HDD PCP PDSI;
%DO J=1 %TO 4 ;
%LET VAR=%SUBSTR(%STR(&VARS),(&J*4-3),3);
%LET V =%SUBSTR(%STR(&VARS),(&J*4-3),1);

DATA ANNUAL;
SET AG.MASTER;
ARRAY  &V{12} &VAR.1-&VAR.12;
DO I=1 TO 12 ;
IF MONTH = I THEN &V{I} = &VAR;
END;
RUN;

%END;
%MEND LONG2WIDE;
```

The collapsing after this should be easy with an  UPDATE statement in a data step. array, .

Here is some sample data:

```data temp (drop = i j) ;
year = 1960;
do j=1 to 3;
year = year+1;
month = 0;
do i=1 to 12;
month+1;
cdd = rand('NORMAL');
hdd = rand('NORMAL');
pcp = rand('NORMAL');
pmdi = rand('NORMAL');
output;
end;
end;
run;
```

Accepted Solutions
Solution
‎06-24-2014 02:32 AM
SAS Employee
Posts: 340

Re: Creating multiple variable groups from arrays

%MACRO LONG2WIDE(data=, out=, vars=, by=, id=) ;

%let numvars=%sysfunc(countw(&vars.));

DATA &out.;

SET &data.;

by &by.;

%DO J=1 %TO &numvars.;

%LET VAR=%SCAN(%STR(&VARS.),&J);

%LET V =&VAR._ARRAY;

ARRAY  &V.{12} &VAR.1-&VAR.12;

retain &V.;

drop &VAR.;

%END;

if first.&by. then do;

%DO J=1 %TO &numvars.;

%LET VAR=%SCAN(%STR(&VARS.),&J);

%LET V =&VAR._ARRAY;

call missing(of &V.

• );
•   %END;

end;

%DO J=1 %TO &numvars.;

%LET VAR=%SCAN(%STR(&VARS.),&J);

%LET V =&VAR._ARRAY;

&V.{&id.}=&VAR.;

%END;

if last.&by. then do;

output;

end;

drop &id.;

RUN;

%MEND LONG2WIDE;

%LONG2WIDE(data=temp, out=result, vars=CDD HDD PCP PMDI, by=year, id=month);

Message was edited by: Gergely Bathó - converted to be more general

All Replies
Solution
‎06-24-2014 02:32 AM
SAS Employee
Posts: 340

Re: Creating multiple variable groups from arrays

%MACRO LONG2WIDE(data=, out=, vars=, by=, id=) ;

%let numvars=%sysfunc(countw(&vars.));

DATA &out.;

SET &data.;

by &by.;

%DO J=1 %TO &numvars.;

%LET VAR=%SCAN(%STR(&VARS.),&J);

%LET V =&VAR._ARRAY;

ARRAY  &V.{12} &VAR.1-&VAR.12;

retain &V.;

drop &VAR.;

%END;

if first.&by. then do;

%DO J=1 %TO &numvars.;

%LET VAR=%SCAN(%STR(&VARS.),&J);

%LET V =&VAR._ARRAY;

call missing(of &V.

• );
•   %END;

end;

%DO J=1 %TO &numvars.;

%LET VAR=%SCAN(%STR(&VARS.),&J);

%LET V =&VAR._ARRAY;

&V.{&id.}=&VAR.;

%END;

if last.&by. then do;

output;

end;

drop &id.;

RUN;

%MEND LONG2WIDE;

%LONG2WIDE(data=temp, out=result, vars=CDD HDD PCP PMDI, by=year, id=month);

Message was edited by: Gergely Bathó - converted to be more general

Super Contributor
Posts: 644

Re: Creating multiple variable groups from arrays

You do not need a macro - just define 4 arrays

data want ;

retain CDD1 - CCD12 HDD1 - HDD12 PCP1 -PCP12 PDSI1 - PDSI12 ;

array CDDX {12} CDD: ;

array HDDX {12} HDD: ;

array PCPX {12} PCP: ;

array PDSIX{12} PDSI: ;

set have ;

by year month ;

CDDX{month} = CDD ;

HDDX{month} = HDD ;

PCPX{month} = PCP ;

PDSIX{month} = PDSI ;

If      Last.year then

do ;

output ;

do m = 1 to 12 ;

call missing (CDDX{m}) ;

call missing (HDDX{m}) ;

call missing (PCPX{m}) ;

call missing (PDSIX{m}) ;

end ;

end ;

drop CDD HDD PCP PDSI ;

run ;

[untested]

Richard

Super Contributor
Posts: 644

Re: Creating multiple variable groups from arrays

Corrected version - tested

data want ;

length year 8 ;

retain CDD1 - CDD12 HDD1 - HDD12 PCP1 -PCP12 PMDI1 - PMDI12 ;

array CDDX {12} CDD: ;

array HDDX {12} HDD: ;

array PCPX {12} PCP: ;

array PMDIX{12} PMDI: ;

set temp ;

by year month ;

CDDX{month} = CDD ;

HDDX{month} = HDD ;

PCPX{month} = PCP ;

PMDIX{month} = PMDI ;

If      Last.year then

do ;

output ;

do m = 1 to 12 ;

call missing (CDDX{m}) ;

call missing (HDDX{m}) ;

call missing (PCPX{m}) ;

call missing (PMDIX{m}) ;

end ;

end ;

drop CDD HDD PCP PMDI month m ;

run ;

Richard

Occasional Contributor
Posts: 7

Re: Creating multiple variable groups from arrays

Thanks Richard! Very helpful. Do you see any opportunity to automate the process (instead of having an array statement for each one etc.). I'm just asking to make it easier to recycle the code and make it more pleasant to use across datasets with hundreds of vars. Thanks again!

Super User
Posts: 9,599

Re: Creating multiple variable groups from arrays

Could I suggest proc transpose, which is what you are trying to achieve e.g.

proc transpose data=work.temp out=work.tran1 (drop=_name_) prefix=cdd;

by year;

var cdd;

id month;

idlabel month;

run;

Then you could SQL join or datastep merge to get the data back together.  May find that easier to re-use.

Alternatively if you are resolute in your need to use arrays then bear in mind you can have multi-level arrays:

array myarray{x,y};

So you could do a proc sql count(distinct name's without number) as x from sashelp.vcolumn...

and proc sql max(number part of name) to get y.

SAS Employee
Posts: 340

Re: Creating multiple variable groups from arrays

Hi Phill, I converted my previous code to be more general.

🔒 This topic is solved and locked.