topic Re: Macro transpose : How to add a 2nd variable in SAS Programming

Macro transpose : How to add a 2nd variable

Liamb — Fri, 15 Apr 2022 10:34:11 GMT

Hello
I use the following macro to transpose my tables according to a single variable and I would like to introduce a second variable.

Can anyone help me for this?

Thanks

%macro transpose_B(	
						vis=,
						vis2=
					 );

			data table_&vis;
			set table;
			if visit=&vis2;
			keep id visit var1 var2 var3 var4 var5 var6 ;
			
		run;
		
		proc sort data=table_&vis nodupkey;
			by id ;
		run;

		
		data table_&vis;
			set table_&vis;
			drop VISIT;
			rename var1=var1_&vis; rename var2=var2_&vis; rename var3=var3_&vis; rename var4=var4_&vis; rename var5=var5_&vis;  rename var6=var6_&vis;
			
		run;

	
	proc sort data=table_&vis;
		by id;
	run;

%mend;

/*Run the transpose_B macro */

%transpose_B(vis=D1, vis2="Baseline");

Re: Macro transpose : How to add a 2nd variable

PaigeMiller — Fri, 15 Apr 2022 10:45:35 GMT

It's not clear to me what you mean by "introduce a second variable". What would this second variable do?

It's also not clear to me how this is a transpose.

Nevertheless, TRANSPOSE macros are available, you don't have to write your own.

https://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/TASS/Tabachneck-FlipTranspose.pdf

https://communities.sas.com/t5/SAS-Communities-Library/A-better-way-to-FLIP-i-e-transpose-make-wide-a-dataset/ta-p/433620

Re: Macro transpose : How to add a 2nd variable

Liamb — Fri, 15 Apr 2022 10:46:40 GMT

I would like to transpose on 2 variables, for example visit and sex

Re: Macro transpose : How to add a 2nd variable

PaigeMiller — Fri, 15 Apr 2022 10:50:14 GMT

Use the %TRANSPOSE macro at the link I gave

Re: Macro transpose : How to add a 2nd variable

ballardw — Fri, 15 Apr 2022 14:58:57 GMT

Repeated use of code like this with the same data set on Set and Data statements in the body of macro code can make it extremely difficult to trace exactly where your logic had an error when debugging the data.

data table_&vis;
   set table_&vis;

I also agree with @PaigeMiller that there is no "transposing" going on here. All I see is sub-setting a dataset and renaming (clumsily) a bunch of variables.

With that in mind, what do you intend to do with that second variable?

Show the code that performed the entire task you are attempting, using two variables, without any macro code. It is extremely difficult to program macros properly without non-macro code that works before attempting the macro. The SAS macro language creates code statements. If you do not know what statements you need to generate then you won't be able to code a macro to create them.

Re: Macro transpose : How to add a 2nd variable

Liamb — Fri, 15 Apr 2022 15:51:58 GMT

I have a dataset as below.

ID	VISIT	DATE	LAB_ID	LAB_V	LAB_U	LAB_NORMAL
A1	D1	15/04/2022	HB	12	d/dl	YES
A1	D2	16/04/2022	CREAT	20	umol/l	YES
A1	D3	17/04/2022	CRP	100	mg/l	NO
A1	D4	18/04/2022	AST	70	U/l	YES
A1	D5	19/04/2022	ALT	90	U/l	NO
A2	D1	20/04/2022	HB	10		YES
A2	D2	21/04/2022	CREAT	30		YES
A2	D3	22/04/2022	CRP	80		YES
A2	D4	23/04/2022	AST	50		YES
A2	D5	24/04/2022	ALT	40		YES
A3	D1	25/04/2022	HB	6		NO
A3	D2	26/04/2022	CREAT	10		NO
A3	D3	27/04/2022	CRP	400		NO
A3	D4	28/04/2022	AST	200		NO
A3	D5	29/04/2022	ALT	200		NO

The macro I sent you allows me to transpose the table and add the name of the visit to the name of each variable then I do a merge of all my tables (table_d1 table_d2 table_d3 table_d4 table_d5).
And I want to transpose my table with visit and lab_id which will give me table_hb_d1 table_hb_d2.......

Re: Macro transpose : How to add a 2nd variable

PaigeMiller — Fri, 15 Apr 2022 15:55:17 GMT

@Liamb The %TRANSPOSE macro that I gave a link to will do this.