PROC TRANSPOSE Multiple Columns

[bendsteel6]

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;

[ed_sas_member]

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,

[bendsteel6]

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!

[art297 · Accepted solution]

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

[bendsteel6]

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!

[ed_sas_member]

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,

[AhmedAl_Attar]

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