BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Can someone please help

I have the following scenerio.I need to create 2 rows out of this data and to put the agt # in one row. I have thousands of these types of records, whats more is my mind is a blank. I figure I need to use call execute but frankly am just stomped. Would appreciate any suggestions. Thanks Lisa

Entered Br Grp Name id srvc Br Agt #
11/03/2010 LA CP Lisa Eberman 12345 LA 6782
11/03/2010 LA CP Lisa Eberman 12345 LA 6166
11/03/2010 LA RP Lisa Eberman 12345 LA 6686
11/03/2010 LA RP Lisa Eberman 12345 LA 6633
11/03/2010 LA RP Lisa Eberman 12345 LA 73660
11/03/2010 LA RP Lisa Eberman 12345 LA 6231
11/03/2010 LA AP Lisa Eberman 12345 LA 1234

11/03/2010 NY RP Ella Temp 56789 LA 6231
11/03/2010 NY RP Ella Temp 56789 LA 6232

11/03/2010 NY MM Mark Wise 09876 NY 64621
11/03/2010 NY MM Mark Wise 09876 NY 64605
11/03/2010 NY MM Mark Wise 09876 NY 64589
11/03/2010 NY MM Mark Wise 09876 NY 64588
11/03/2010 NY MM Mark Wise 09876 NY 64586

11/03/2010 NY AB Liza White 19876 XY 64588
11/03/2010 NY AB Liza White 19876 XY 64586
9 REPLIES 9
deleted_user
Not applicable
Duhhh-

What I want it to look like is like this...
11/03/2010 LA CP Lisa Eberman 12345 LA 6782,6166
11/03/2010 LA RP Lisa Eberman 12345 LA 6686,6633,73660,6231
11/03/2010 LA AP Lisa Eberman 12345 LA 1234
11/03/2010 NY RP Ella Temp 56789 LA 6231,6232
11/03/2010 NY MM Mark Wise 9876 NY 64621,64605,64589,64588,64586
11/03/2010 NY AB Liza White 19876 XY 64588,64586
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Consider using PROC TRANSPOSE to get to a "horizontal output condition" for your Agt# variable. Then you can use a SAS DATA step to create a concatenated string of all Agt# variables, using a SAS assignment statement and also using the CATX(',' OF AGT#: ) function - set the LENGTH of your concatenated Agt# values first.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

catx function concatenate data values site:sas.com
deleted_user
Not applicable
What I actually did is put all those fields (minus the Agt #) into a large field and did the proc transpose as you mentioned. But I think that there has got to be a better way.
SPR
Quartz | Level 8 SPR
Quartz | Level 8
Hello LisaEb,

Is this what you need?:
[pre]
data i;
input Entered mmddyy10. Br $ Grp $ Name1 $ Name2 $ id srvc $ Agt;
format Entered date7.;
datalines;
11/03/2010 LA CP Lisa Eberman 12345 LA 6782
11/03/2010 LA CP Lisa Eberman 12345 LA 6166
11/03/2010 LA RP Lisa Eberman 12345 LA 6686
11/03/2010 LA RP Lisa Eberman 12345 LA 6633
11/03/2010 LA RP Lisa Eberman 12345 LA 73660
11/03/2010 LA RP Lisa Eberman 12345 LA 6231
11/03/2010 LA AP Lisa Eberman 12345 LA 1234
11/03/2010 NY RP Ella Temp 56789 LA 6231
11/03/2010 NY RP Ella Temp 56789 LA 6232
11/03/2010 NY MM Mark Wise 09876 NY 64621
11/03/2010 NY MM Mark Wise 09876 NY 64605
11/03/2010 NY MM Mark Wise 09876 NY 64589
11/03/2010 NY MM Mark Wise 09876 NY 64588
11/03/2010 NY MM Mark Wise 09876 NY 64586
11/03/2010 NY AB Liza White 19876 XY 64588
11/03/2010 NY AB Liza White 19876 XY 64586
;
run;
proc sort data=i out=s;
by Entered Br Grp Name1 Name2 id srvc;
run;
proc transpose data=s out=r(drop=_name_) prefix=Agt;
var agt;
by Entered Br Grp Name1 Name2 id srvc;
run;
[/pre]
Sincerely,
SPR
deleted_user
Not applicable
I feel INCREDIBLY stupid...in my X years of experience I never had the need and assumed that there was a limitation to the # of fields to put on the by field in PROC TRANSPOSE. I am not even going to embarrass myself by telling how many years of SAS exp I have.


Yes this seems to have done the trick...

May I trouble with a follow up question I am trying to build a catx function

AGTLIST = CATX (',',COL1,COL2,COL3,COL4....);
This code will be in production. I want it to be dynamic… I am doing something wrong.

%MACRO CATX_STR;
DATA TESTCODE2;
SET TESTCODE;
AGTLIST = CATX(',',
%DO I = 1 %TO &MAX_AGTS;
agt&I ,
%END;
);;
RUN;
%MEND CATX_STR;
%CATX_STR;
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
As suggested, use the "OF" keyword with CATX function and a variable stem/prefix - shown below - also covered in the SAS 9.2 DOC on CATX:

ALL_VALUES = CATX(',',OF COL: );

Scott Barry
SBBWorks, Inc. Message was edited by: sbb
Cynthia_sas
SAS Super FREQ
Hi:
The CATX function will take a numbered list, as shown below. Do you need a macro program for some other reason??

cynthia
[pre]
data coldata(keep=col1 col2 col3 sales);
length col1 col2 col3 $25;
set sashelp.shoes;
col1 = region;
col2 = subsidiary;
col3 = product;
output;
run;

data new;
length newvar $80;
set coldata;
newvar=catx(',',of col1-col3);
run;

ods listing;
proc print data=new;
var newvar col1 col2 col3 sales;
run;
[/pre]
SPR
Quartz | Level 8 SPR
Quartz | Level 8
Hello LisaEb,

There is a small mistake in your code, namely comma position is wrong. The correct code looks like this:
[pre]
%MACRO CATX_STR;
DATA TESTCODE2;
SET TESTCODE;
AGTLIST = CATX(','
%DO I = 1 %TO &MAX_AGTS;
,agt&I
%END;
);
RUN;
%MEND CATX_STR;
%CATX_STR;
[/pre]
Sincerely,
SPR
Ksharp
Super User
Hi.
I have another way.Just to be a choice.


[pre]
data i;
input Entered mmddyy10. Br $ Grp $ Name & $20. id srvc $ Agt;
format Entered mmddyy10.;
datalines;
11/03/2010 LA CP Lisa Eberman 12345 LA 6782
11/03/2010 LA CP Lisa Eberman 12345 LA 6166
11/03/2010 LA RP Lisa Eberman 12345 LA 6686
11/03/2010 LA RP Lisa Eberman 12345 LA 6633
11/03/2010 LA RP Lisa Eberman 12345 LA 73660
11/03/2010 LA RP Lisa Eberman 12345 LA 6231
11/03/2010 LA AP Lisa Eberman 12345 LA 1234
11/03/2010 NY RP Ella Temp 56789 LA 6231
11/03/2010 NY RP Ella Temp 56789 LA 6232
11/03/2010 NY MM Mark Wise 09876 NY 64621
11/03/2010 NY MM Mark Wise 09876 NY 64605
11/03/2010 NY MM Mark Wise 09876 NY 64589
11/03/2010 NY MM Mark Wise 09876 NY 64588
11/03/2010 NY MM Mark Wise 09876 NY 64586
11/03/2010 NY AB Liza White 19876 XY 64588
11/03/2010 NY AB Liza White 19876 XY 64586
;
run;

proc sort data=i;
by entered br grp ;

data result;
set i;
by entered br grp;
length whole $ 200;
retain whole;
if first.grp then call missing(whole);
whole=catx(',',whole,agt);
if last.grp then output;
run;
proc print noobs; run;
[/pre]




Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 9 replies
  • 953 views
  • 0 likes
  • 5 in conversation