topic Re: Help with Transpose function in SAS Programming

Help with Transpose function

Vidanovic — Wed, 09 Jun 2021 07:43:59 GMT

Dear SAS users,

Is there any Advance SAS user which can help me transpose some data.
Here is the example of starting table how it looks:

ACCOUNT_ID	APPLICATION_ID	K_202001	J_202001	Z_202001	K_202002	J_202002	Z_202002	K_202003	J_202003	Z_202003
1	10	1	2	3	11	12	13	21	22	23
2	20	4	5	6	14	15	16	24	25	26
3	30	7	8	9	17	18	19	27	28	29

And this is the results I want to have:

DATE	ACCOUNT_ID	APPLICATION_ID	K	J	Z
202001	1	10	1	2	3
202001	2	20	4	5	6
202001	3	30	7	8	9
202002	1	10	11	12	13
202002	2	20	14	15	16
202002	3	30	17	18	19
202003	1	10	21	22	23
202003	2	20	24	25	26
202003	3	30	27	28	29

Thank you in advance brothers and sisters 🙂

Kind regards,
Nikola

Re: Help with Transpose function

andreas_lds — Wed, 09 Jun 2021 09:07:25 GMT

Please post data in usable form, those tables are nice to look at, but lack information about type and length of the variables.

This could be a solution:

data transposed;
	set have;
	
	length dateStr $ 6 variable $ 1 value 8 VarName $ 32;
	
	array vars K_202001--Z_202003;
	
	do i = 1 to dim(vars);
		VarName = vname(vars[i]);
		variable = first(VarName);
		dateStr = scan(VarName, 2, '_');
		value = vars[i];
		output;
	end;
	
	keep dateStr variable value Account_id Application_ID;
run;

proc sort data=work.transposed;
	by  dateStr Account_id Application_ID variable;
run;

proc transpose data=transposed out=want(drop=_name_);
	by dateStr Account_id Application_ID ;
	var value;
	id variable;
run;