Creating multiple variable groups from arrays

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

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

    View solution in original post


    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
    Super User
    Posts: 7,392

    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.

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

    Discussion stats
    • 6 replies
    • 322 views
    • 9 likes
    • 4 in conversation