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;
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
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,
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!
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
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!
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,
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: