Transpose Macro Issue

ybz12003 — Tue, 15 Jan 2019 13:48:20 GMT

Hello:

I ran a macro for transposing dataset. The code is list below.

options mprint symbolgen mlogic;

%let vars = TestRGdt1 TestRGtype1 TestRGlot1 TestRGdt2  TestRGtype2 TestRGlot2 TestRGnum
                  TestPVnum TestPVdt1 TestPVtype1 TestPVlot1 TestPVdt2 TestPVtype2 TestPVlot2;

%macro tranpose();
      %do i = 1 %to %sysfunc(countw(&vars));
            %let v = %scan(&vars., &i.);

            proc transpose data = Test_2010 out = temp2010 prefix = &v._20;
                  by ID;
                  id Testseason;
                  var &v. ;
            run;

			proc transpose data = Test2009Final out = temp2009 prefix = &v._20;
                  by ID;
                  id seasontail;
                  var &v. ;
            run;

			proc transpose data = Test_2008 out = temp2008 prefix = &v._200;
                  by ID;
                  id Testseason;
                  var &v. ;
            run;


      %end;
%mend;

%tranpose();

The Log result is shown below.

MLOGIC(TRANPOSE): Beginning execution.

SYMBOLGEN: Macro variable VARS resolves to TestRGdt1 TestRGtype1 TestRGlot1 TestRGdt2

TestRGtype2 TestRGlot2 TestRGnum TestPVnum TestPVdt1

TestPVtype1 TestPVlot1 TestPVdt2 TestPVtype2 TestPVlot2

MLOGIC(TRANPOSE): %DO loop beginning; index variable I; start value is 1; stop value is 14; by value

is 1.

MLOGIC(TRANPOSE): %LET (variable name is V)

SYMBOLGEN: Macro variable VARS resolves to TestRGdt1 TestRGtype1 TestRGlot1 TestRGdt2

TestRGtype2 TestRGlot2 TestRGnum TestPVnum TestPVdt1

TestPVtype1 TestPVlot1 TestPVdt2 TestPVtype2 TestPVlot2

SYMBOLGEN: Macro variable I resolves to 1

SYMBOLGEN: Macro variable V resolves to TestRGdt1

MPRINT(TRANPOSE): proc transpose data = Test_2010 out = temp2010 prefix = TestRGdt1_20;

MPRINT(TRANPOSE): by ID;

MPRINT(TRANPOSE): id Testseason;

SYMBOLGEN: Macro variable V resolves to TestRGdt1

MPRINT(TRANPOSE): var TestRGdt1 ;

MLOGIC(TRANPOSE): %DO loop index variable I is now 5; loop will iterate again.

MLOGIC(TRANPOSE): %LET (variable name is V)

SYMBOLGEN: Macro variable VARS resolves to TestRGdt1 TestRGtype1 TestRGlot1 TestRGdt2

TestRGtype2 TestRGlot2 TestRGnum TestPVnum TestPVdt1

TestPVtype1 TestPVlot1 TestPVdt2 TestPVtype2 TestPVlot2

SYMBOLGEN: Macro variable I resolves to 5

SYMBOLGEN: Macro variable V resolves to TestRGtype2

MPRINT(TRANPOSE): proc transpose data = Test_2010 out = temp2010 prefix = TestRGtype2_20;

MPRINT(TRANPOSE): by ID;

MPRINT(TRANPOSE): id Testseason;

SYMBOLGEN: Macro variable V resolves to TestRGtype2

MPRINT(TRANPOSE): var TestRGtype2 ;

MPRINT(TRANPOSE): run;

MLOGIC(TRANPOSE): %DO loop index variable I is now 14; loop will iterate again.

MLOGIC(TRANPOSE): %LET (variable name is V)

SYMBOLGEN: Macro variable VARS resolves to TestRGdt1 TestRGtype1 TestRGlot1 TestRGdt2

TestRGtype2 TestRGlot2 TestRGnum TestPVnum TestPVdt1

TestPVtype1 TestPVlot1 TestPVdt2 TestPVtype2 TestPVlot2

SYMBOLGEN: Macro variable I resolves to 14

SYMBOLGEN: Macro variable V resolves to TestPVlot2

MPRINT(TRANPOSE): proc transpose data = Test_2010 out = temp2010 prefix = TestPVlot2_20;

MPRINT(TRANPOSE): by ID;

MPRINT(TRANPOSE): id Testseason;

SYMBOLGEN: Macro variable V resolves to TestPVlot2

MPRINT(TRANPOSE): var TestPVlot2 ;

MPRINT(TRANPOSE): run;

However, I checked the final dataset 'Temp2010' was not right. I only saw the last variable 'TestPVlot2' shown in the dataset. All the other 13 variables, such as 'TestRGdt1 TestRGtype1 TestRGlot1 TestRGdt2 TestRGtype2 TestRGlot2 TestRGnum TestPVnum TestPVdt1 TestPVtype1 TestPVlot1 TestPVdt2 TestPVtype2', were missing in the 'Temp2010'. As you could see the log window, the program DID ruthroughht all of the 14 variables. Any idea where went wrong and how to fix it? Thank you!!!

Re: Transpose Macro Issue

Kurt_Bremser — Tue, 15 Jan 2019 13:56:30 GMT

You overwrite your output datasets in every macro iteration, so only the last iteration can take effect.

You can do all variables in one step, and use the _NAME_ variable to identify the source columns.

Re: Transpose Macro Issue

ybz12003 — Tue, 15 Jan 2019 14:02:55 GMT

Hi;

I ran the codes below, but the error was shown in the log. Could you specify where to put the '_NAME_'? I am still confused.

%macro tranpose();
      %do i = 1 %to %sysfunc(countw(&vars));
            %let v = %scan(&vars., &i.);

            proc transpose data = FluPS_2010 out = temp2010 prefix = &v._20;
                  by caseid;
                  id fluPSseason;
                  var _NAME_ ;
            run;

%end;
%mend;

%tranpose();

Re: Transpose Macro Issue

Kurt_Bremser — Tue, 15 Jan 2019 14:22:35 GMT

I strongly suugest you acquaint yourself with the Transpose Procedure first before even thinking about using it in a macro. The documentation will tell you what _NAME_ is.

topic Re: Transpose Macro Issue in SAS Programming

Transpose Macro Issue

Re: Transpose Macro Issue

Re: Transpose Macro Issue

Re: Transpose Macro Issue