BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bendsteel6
Obsidian | Level 7

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

View solution in original post

6 REPLIES 6
ed_sas_member
Meteorite | Level 14

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
Obsidian | Level 7

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
Opal | Level 21

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
Obsidian | Level 7

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
Meteorite | Level 14

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;

Capture d’écran 2020-04-24 à 08.53.39.png

 

 

 

 

 

Best,

AhmedAl_Attar
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1241 views
  • 1 like
  • 4 in conversation