topic Re: A data manupulation question in SAS Procedures

A data manupulation question

need_sas_help — Wed, 12 Nov 2014 18:33:47 GMT

I have a following datatset with two character variable (subject, arm)

subject       arm
-------       ----
10001          PA
10001          CA
10001          CA
10002          NA
10002          PA
10002          NA

I need a following dataset where arm variable will be the combination of every unique value
for each subject.
The result dataset should be as below.:

subject       arm
-------       -----
10001          PA + CA
10002          NA + PA

I know it possible to do it using proc sort using nodupkey and then merging and using catx
function. But that will be a very long ugly code. Will you be kind enough
to help me with a more efficient code?

Thank you so much.

Re: A data manupulation question

SASKiwi — Wed, 12 Nov 2014 18:55:33 GMT

Something like this (untested):

data want;

set have;

by subject;

retain arm_new;

if first.subject then arm_new = '';

arm_new = catx(' + ',arm);

if last.subject then output;

run;

Re: A data manupulation question

Haikuo — Wed, 12 Nov 2014 18:56:32 GMT

I won't say it would be very long and ugly if using proc sort first. but you do have other options.

data have;

input subject$ arm$50.;

cards;

10001 PA

10001 CA

10002 NA

10002 PA

10002 NA

;

proc sort data=have out=have1 nodupkey;

by subject arm;

run;

data want;

length arm $ 50;

do until (last.subject);

set have1 (rename= arm=_arm);

by subject;

arm=catx('+', arm, _arm);

end;

drop _arm;

run;

/*array : assume your data is at least clustered by subject*/

data want_array;

length arm $ 50;

array t(100)/*100 is arbitary, using Hash to obtain full dynamic*/ $ 2;

do _n_=1 by 1 until (last.subject);

set have (rename=arm=_arm);

by subject notsorted;

if _arm not in t then t(_n_)=_arm;

end;

arm=catx('+', of t(*));

keep subject arm;

run;

Haikuo

Re: A data manupulation question

stat_sas — Wed, 12 Nov 2014 19:07:29 GMT

proc sql;

create table want as

select distinct subject,arm from have

order by subject;

quit;

data need;

set want;

by subject;

retain arm;

arm=catx(' + ',lag(arm),arm);

if last.subject;

run;

Re: A data manupulation question

PGStats — Wed, 12 Nov 2014 19:08:11 GMT

Is this good enough?

data have;

input subject $ arm $;

datalines;

10001 PA

10001 CA

10002 NA

10002 PA

10002 NA

;

proc sort data=have; by subject arm; run;

data want(rename=dum=arm);

length dum $200;

do until(last.subject);

set have; by subject arm;

if last.arm then dum = catx(" + ", dum, arm);

end;

run;

proc print data=want noobs; run;

Re: A data manupulation question

Astounding — Wed, 12 Nov 2014 19:13:25 GMT

PG Stats,

I know my SQL is rudimentary at best, but wouldn't something like this work, even on unsorted data?

proc sql noprint;

select subject, distinct arm separated by ' + ' as all_arm

from have

group by subject;

quit;

Re: A data manupulation question

need_sas_help — Wed, 12 Nov 2014 19:37:29 GMT

Astounding.. looks good.. but unfortunately given an error.

2079 proc sql noprint;

2080 select caseno, distinct drnm01 separated by ' + ' as all_arm

------

202

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **,

+, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT,

FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH,

LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

2081 from &RAWLIB..A02DR

2082 group by caseno;

2083 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

Re: A data manupulation question

DBailey — Wed, 12 Nov 2014 19:43:28 GMT

separated by used when building a macro variable

Re: A data manupulation question

CharlotteCain — Wed, 12 Nov 2014 19:53:28 GMT

Hi,

May i request you to please explain the execution of a portion of your code :

/*array : assume your data is at least clustered by subject*/

data want_array;

length arm $ 50;

array t(100)/*100 is arbitary, using Hash to obtain full dynamic*/ $ 2;

do _n_=1 by 1 until (last.subject);

set have (rename=arm=_arm);

by subject notsorted;

if _arm not in t then t(_n_)=_arm;/*this part please? thanks*/

end;

arm=catx('+', of t(*));

keep subject arm;

run;

Re: A data manupulation question

PGStats — Wed, 12 Nov 2014 20:04:29 GMT

This type of concatenation is only available when selecting INTO a macro variable. As in

Proc sql;

select distinct arm into :myArmList separated by " + "

from have;

quit;

Re: A data manupulation question

Haikuo — Wed, 12 Nov 2014 20:08:25 GMT

I can't find the official online doc myself at the moment,

if _arm not in t /*this t is referring array t*/

then t(_n_)=_arm;

it basically only allow distinct _arm entering array t.

Let me know if I have myself clear enough.

Regards,

Haikuo

Re: A data manupulation question

Kurt_Bremser — Thu, 13 Nov 2014 07:58:17 GMT

Actually, IMO your stated solution is the most elegant one. All others tend to be more complicated or obfuscated.

Re: A data manupulation question

PGStats — Thu, 13 Nov 2014 15:38:44 GMT

The IN operator involving an array is documented here:

SAS(R) 9.4 Language Reference: Concepts, Fourth Edition

Re: A data manupulation question

SR_FR — Thu, 13 Nov 2014 16:20:34 GMT

a proposal...

The code won't work on unsorted data...

With a a PROC SORT NODUPKEYS, the code will be more simple...

best regards

Re: A data manupulation question

Paul65 — Fri, 14 Nov 2014 02:26:30 GMT

I tried using hash objects and could not get it to work. But I believe the following code works using perl regular expressions:

data temp_1;

input subject $ arm $;

cards;

10000 NJ

10001 PA

10001 CA

10002 NA

10002 PA

10002 NA

10003 NJ

10004 NJ

;

data temp_2;

set temp_1 end=last;

length old_subject $ 5

arm_list $ 100;

retain old_subject

arm_list;

keep old_subject

arm_list;

rename old_subject = subject

arm_list = arm;

if _N_ = 1 then do;

old_subject = subject;

arm_list = arm;

end;

if old_subject = subject then do;

if ^prxmatch('/' || strip(arm) || '/', arm_list) then

arm_list = strip(arm_list) || ' + ' || strip(arm);

end;

else do;

arm_list = prxchange('s/^[ +]+(.*)/$1/', 1, arm_list);

output;

arm_list = arm;

old_subject = subject;

end;

if last then

output;

run;

Re: A data manupulation question

Haikuo — Fri, 14 Nov 2014 03:49:08 GMT

If choose to use Hash:

data temp_1;

input subject $ arm $;

cards;

10000 NJ

10001 PA

10001 CA

10002 NA

10002 PA

10002 NA

10003 NJ

10004 NJ

;

data want;

declare hash h();

h.definekey('arm');

h.definedata('arm');

h.definedone();

declare hiter hi('h');

do until (last.subject);

set temp_1;

by subject notsorted;

length arm_list $ 100;

rc=h.add();

end;

do rc=hi.first() by 0 while (rc=0);

arm_list=catx(',',arm_list,arm);

rc=hi.next();

end;

drop arm rc;

run;

Re: A data manupulation question

Kurt_Bremser — Fri, 14 Nov 2014 07:33:12 GMT

The sort nodupkey and data step solution;

data have;

infile cards;

length subject 5 arm $2;

input subject arm;

cards;

10001 PA

10001 CA

10002 NA

10002 PA

10002 NA

;

run;

proc sort data=have nodupkey; by subject arm; run;

data want (drop=arm rename=(arm_new=arm));

set have;

by subject;

length arm_new $ 20;

retain arm_new;

if first.subject then arm_new = '';

arm_new = catx(' + ',arm_new,arm);

if last.subject then output;

run;

Can't see what's long and ugly here.

Re: A data manupulation question

Haikuo — Fri, 14 Nov 2014 14:11:36 GMT

Kurt, Similar solution (as well as the similar comments) has already been provided in the second answer (Nov 12, 2014 1:56 PM). I think we are too hash on the OP who is probably a SAS beginner.

Re: A data manupulation question

Kurt_Bremser — Mon, 17 Nov 2014 06:13:12 GMT

The second post wouldn't really work, because arm_new was not included in the catx function. I basically delivered mine to rectify this small mistake. And to show that, as it often is with SAS, the simple data step solution is also the shortest and most easy to comprehend.

Re: A data manupulation question

Haikuo — Mon, 17 Nov 2014 14:39:11 GMT

Well, here is the typical scenario of 'lost in translation', and I agree it is hard to follow every post in a long thread. when I say "second answer (Nov 12, 2014 1:56 PM). ", it is the second answer, not the second post. That is why I have attached time stamp as well.

The reason I want this to be straightened is that not everyone on this forum is experienced programmer, and the last thing we want is to have them confused.

Haikuo