topic PROC TRANSPOSE Multiple Columns in SAS Programming

PROC TRANSPOSE Multiple Columns

bendsteel6 — Thu, 23 Apr 2020 14:46:22 GMT

Hi. I'm trying to transpose the data in my 'have' to my 'want' datasets (tables can be built from the code below). Is it possible to group by the CLAIM_JOB_ID and have all the INC_MTR_NO's and QTY's on one line? I've looked at many of these types of posts and haven't found one that's quite the same.

data have;

infile datalines delimiter=',' dsd;

length claim_job_id $20. inc_mtr_no $10. Qty $3.;

input claim_job_id inc_mtr_no Qty ;

datalines;

7698-2720K-282-1,990950,100

7698-2720K-282-1,GRO836029,100

7698-2826K-316292-2,FV HC19350,10

7698-2826K-316292-2,FV HC19375,10

7698-2826K-317055-3,GRO833100,100

7698-2826K-317055-3,GRO833101,100

7698-2826K-317108-2,GRO833100,100

7698-2826K-317108-2,GRO833101,100

7698-2826K-319505-1,FV HC19350,10

7698-2826K-319505-1,FV HC19375,10

7698-2826K-325714-8,GRO833100,100

7698-2826K-325714-8,GRO833101,100

7698-2826K-331417-1,GRO833100,100

7698-2826K-331417-1,GRO833101,100

7698-2826K-331425-1,GRO833100,100

7698-2826K-331425-1,GRO833101,100

7698-2826K-331425-1,GRO833133,50

;

run;

data want;

infile datalines delimiter=',' dsd;

retain CLAIM_JOB_ID INC_MTR_NO1 Qty1 INC_MTR_NO2 Qty2 INC_MTR_NO3 Qty3;

length CLAIM_JOB_ID $20.

INC_MTR_NO1 INC_MTR_NO2 INC_MTR_NO3 $10.

Qty1 Qty2 Qty3 $3.;

input CLAIM_JOB_ID INC_MTR_NO1 Qty1 INC_MTR_NO2 Qty2 INC_MTR_NO3 Qty3;

datalines;

7698-2720K-282-1,990950,100,GRO836029,100,,

7698-2826K-316292-2,FV HC19350,10,FV HC19375,10,,

7698-2826K-317055-3,GRO833100,100,GRO833101,100,,

7698-2826K-317108-2,GRO833100,100,GRO833101,100,,

7698-2826K-319505-1,FV HC19350,10,FV HC19375,10,,

7698-2826K-325714-8,GRO833100,100,GRO833101,100,,

7698-2826K-331417-1,GRO833100,100,GRO833101,100,,

7698-2826K-331425-1,GRO833100,100,GRO833101,100,GRO833133,50

;

run;

Re: PROC TRANSPOSE Multiple Columns

ed_sas_member — Thu, 23 Apr 2020 14:56:01 GMT

Hi @bendsteel6

Here is an approach to achieve this:

proc transpose data=have out=_want1 (drop=_:) prefix=inc_mtr_no;
	var inc_mtr_no;
	by claim_job_id;
run;
proc transpose data=have out=_want2 (drop=_:) prefix=Qty;
	var Qty;
	by claim_job_id;
run;
data want;
	merge _want1 _want2;
	by claim_job_id;
run;

My best,

Re: PROC TRANSPOSE Multiple Columns

bendsteel6 — Thu, 23 Apr 2020 15:16:59 GMT

Hi, thanks for your quick response.

But this will give me the following columns:

CLAIM_JOB_ID INC_MTR_NO1 INC_MTR_NO2 INC_MTR_NO3 QTY1 QTY2 QTY3.

I need them in this order:

CLAIM_JOB_ID INC_MTR_NO1 QTY1 INC_MTR_NO2 QTY2 INC_MTR_NO3 QTY3 (and possibly more if the 'have' dataset has more than 3 instances of the BY variable.

I guess I should've specified this at the start...sorry.

Would there be an easy way to fix this?

Thanks!

Re: PROC TRANSPOSE Multiple Columns

art297 — Thu, 23 Apr 2020 15:47:53 GMT

A simple retain statement would let you reorder the variables.

e.g.:

data want;
  retain claim_job_id inc_mtr_no1 Qty1 inc_mtr_no2 Qty2 inc_mtr_no3 Qty3;
  set want;
run;

Conversely, the following will do the task and output the variables in the desired order:

filename tr url 'https://raw.githubusercontent.com/art297/transpose/master/transpose.sas';
%include tr ;

%transpose(data=have, out=want, by=claim_job_id,
  var=inc_mtr_no Qty)

Art, CEO, AnalystFinder.com

Re: PROC TRANSPOSE Multiple Columns

AhmedAl_Attar — Thu, 23 Apr 2020 17:16:03 GMT

Here is another more approach

data have;
	infile datalines delimiter=',' dsd;
	length claim_job_id $20. inc_mtr_no $10. Qty $3.;
	input claim_job_id inc_mtr_no Qty;
	datalines;
7698-2720K-282-1,990950,100
7698-2720K-282-1,GRO836029,100
7698-2826K-316292-2,FV HC19350,10
7698-2826K-316292-2,FV HC19375,10
7698-2826K-317055-3,GRO833100,100
7698-2826K-317055-3,GRO833101,100
7698-2826K-317108-2,GRO833100,100
7698-2826K-317108-2,GRO833101,100
7698-2826K-319505-1,FV HC19350,10
7698-2826K-319505-1,FV HC19375,10
7698-2826K-325714-8,GRO833100,100
7698-2826K-325714-8,GRO833101,100
7698-2826K-331417-1,GRO833100,100
7698-2826K-331417-1,GRO833101,100
7698-2826K-331425-1,GRO833100,100
7698-2826K-331425-1,GRO833101,100
7698-2826K-331425-1,GRO833133,50
;
run;

/* Find the records count by the ID variable */
proc sql noprint;
	select distinct count(claim_job_id) into :g_max separated by ',' from have 
		group by claim_job_id;
quit;

/* Get the maximum count of records */
%put &=g_max;
%let g_max=%sysfunc(max(&g_max));
%put &=g_max;

/* Declare iteration macro program for use within RETAIN Statement */
%macro iterate();
	%do v=1 %to &g_max;
		inc_mtr_no&v qty&v
	%end;
%mend;

data want(DROP=inc_mtr_no qty i);
	RETAIN claim_job_id %iterate;
	ARRAY inc_mtr_nos {3} $10 inc_mtr_no1-inc_mtr_no&g_max;
	ARRAY qties {3} $3 qty1-qty&g_max;

	do until (last.claim_job_id);
		set have;
		by claim_job_id;
		i+1;
		inc_mtr_nos[i]=inc_mtr_no;
		qties[i]=qty;
	end;
	i=0;
run;

Hope this helps,

Ahmed

Re: PROC TRANSPOSE Multiple Columns

ed_sas_member — Fri, 24 Apr 2020 06:54:24 GMT

Hi @bendsteel6

I have adapted the program to get the columns in the "right" order:

proc transpose data=have out=_want1 (drop=_:) prefix=inc_mtr_no;
	var inc_mtr_no;
	by claim_job_id;
run;
proc transpose data=have out=_want2 (drop=_:) prefix=Qty;
	var Qty;
	by claim_job_id;
run;

proc sql noprint;
	select name
	into: list_var separated by " "
	from dictionary.columns
	where libname = "WORK" and memname in ("_WANT1", "_WANT2") and name ne "claim_job_id"
	order by substr(name, length(name));
quit;

data want;
	retain claim_job_id &list_var.;
	merge _want1 _want2;
	by claim_job_id;
run;

Best,

Re: PROC TRANSPOSE Multiple Columns

bendsteel6 — Fri, 24 Apr 2020 11:48:26 GMT

Hi,

Thanks for this. Works perfectly! The other solutions also work but I'll use this as it gives me much more flexibility in the future.

Thanks!