topic Re: DataStep help in SAS Programming

DataStep help

deleted_user — Mon, 13 Dec 2010 19:18:17 GMT

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

Re: DataStep help

deleted_user — Mon, 13 Dec 2010 19:29:52 GMT

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

Re: DataStep help

sbb — Mon, 13 Dec 2010 20:18:24 GMT

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

Re: DataStep help

deleted_user — Mon, 13 Dec 2010 20:26:51 GMT

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.

Re: DataStep help

SPR — Mon, 13 Dec 2010 20:58:46 GMT

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

Re: DataStep help

deleted_user — Mon, 13 Dec 2010 21:10:42 GMT

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;

Re: DataStep help

sbb — Mon, 13 Dec 2010 21:36:01 GMT

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

Re: DataStep help

Cynthia_sas — Mon, 13 Dec 2010 21:44:45 GMT

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]

Re: DataStep help

Ksharp — Tue, 14 Dec 2010 04:08:44 GMT

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

Re: DataStep help

SPR — Tue, 14 Dec 2010 17:29:49 GMT

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