topic Re: I have a real time scenario in SAS Procedures

I have a real time scenario

sahaji — Mon, 20 May 2013 18:05:01 GMT

Hi
I have a dataset with the following info

1234 30004 USD

1234 30012 USD

1289 30056 USD

1389 45009 INR

1456 56789 INR

1456 30067 USD

1567 30099 USD

1567 67890 GBP

1567 30088 INR

1567 30092 INR

I need ouput like this

1234 USD

1289 USD

1389 INR

1456 INR,USD

1567 USD,GBP,INR

how to do it,please help me

Re: I have a real time scenario

Haikuo — Mon, 20 May 2013 18:29:51 GMT

data have;

input id1 $ id2 $ currency $;

cards;

1234 30004 USD

1234 30012 USD

1289 30056 USD

1389 45009 INR

1456 56789 INR

1456 30067 USD

1567 30099 USD

1567 67890 GBP

1567 30088 INR

1567 30092 INR

;

proc sql;

select max (ct) into :ct from (select count(*) as ct from have group by id1) ;quit;

data want;

array t(&ct) $3 _temporary_;

call missing(of t(*));

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

set have;

by id1 notsorted;

if currency not in t then

t(_n_)=currency;

end;

cat_currency=catx(',', of t(*));

keep id1 cat_currency;

run;

As you can see, this code is not as efficient as I want it to be, an additional pre-process has to be done to get the range of array().

Haikuo

Re: I have a real time scenario

sascom10 — Mon, 20 May 2013 18:34:07 GMT

Alterrnate solution -

proc sql;
create table want1 as
    select id
   , currency
   from have
group by 1,2
    order by 1,2;
quit;

proc transpose data=want1 out=want_wide;
by id;
var currency;
run;

data want_final;
set want_wide;
currency1=trim(col1)||' '||trim(col2)||' '||trim(col3);
run;

Re: I have a real time scenario

Haikuo — Mon, 20 May 2013 18:39:20 GMT

I have over-complicated your problem, just simply do:

data want1;

do until (last.id1);

set have;

by id1 notsorted;

length cat_currency $50;

if findw(cat_currency,currency)=0 then cat_currency=catx(',',cat_currency,currency);

end;

keep id1 cat_currency;

run;

No array or macro variable is needed.

Haikuo

Re: I have a real time scenario

sahaji — Mon, 20 May 2013 18:39:44 GMT

Thanks for the reply and can you please provide the code using base sas

Re: I have a real time scenario

sahaji — Mon, 20 May 2013 19:12:10 GMT

I tried like this, but i am not getting accurate answer

The actual data is in dataset smple

DATA SAMPLE1;

LENGTH CURRENCY CURRENCY1 $50;

SET SAMPLE;

BY CUST_ID;

IF FIRST.CUST_ID THEN CURRENCY1='';

PUT _ALL_;

CURRENCY1=CATX(',',trim(CURRENCY1),trim(CURRENCY));

IF LAST.CUST_ID;

RUN;

Re: I have a real time scenario

PGStats — Mon, 20 May 2013 20:02:57 GMT

You need to check for currency to already be in currency1. Also, trim is not necessary in in catx arguments, but it is in findw word argument. Most importantly, you must use a retain statement on currency1 because otherwise it is reset to missing at every iteration. Try it this way :

DATA SAMPLE1;

LENGTH CURRENCY CURRENCY1 $50;

retain currency1;

SET SAMPLE;

BY CUST_ID;

IF FIRST.CUST_ID THEN call missing(CURRENCY1);

if findw(currency1,trim(currency))=0 then CURRENCY1=CATX(',', CURRENCY1, CURRENCY);

IF LAST.CUST_ID;

RUN;

Re: I have a real time scenario

avishek2411 — Sat, 27 May 2017 20:37:17 GMT

data temp;
input id zip cntr $;
cards;
1234 30004 USD
1234 30012 USD
1289 30056 USD
1389 45009 INR
1456 56789 INR
1456 30067 USD
1567 30099 USD
1567 67890 GBP
1567 30088 INR
1567 30092 INR
;

proc sort data=temp(drop=zip) out=temp1 nodupkey;
by id cntr;
run;

data temp2(drop=cntr);
set temp1;
retain cntry;
length cntry $20.;
by id cntr;
if first.id and last.id then do;
cntry=cntr;
output temp2;
end;
else if first.id and last.id = 0 then do;
cntry=cntr;
end;
else if first.id = 0 and last.id = 0 then do;
cntry = catx(',',cntry,cntr);
end;
else if first.id = 0 and last.id = 1 then do;
cntry = catx(',',cntry,cntr);
output temp2;
end;
run;

Re: I have a real time scenario

mk0459 — Thu, 11 Oct 2018 20:02:03 GMT

data d;
input ID SAL CU$;
datalines;
1234 30004 USD

1234 30012 USD

1289 30056 USD

1389 45009 INR

1456 56789 INR

1456 30067 USD

1567 30099 USD

1567 67890 GBP

1567 30088 INR

1567 30092 INR
;
run;

data s;
length curr_joined $ 50;
retain curr_joined ;
set d;

by id;
if first.ID then curr_joined=CU;

else if not exist(curr_joined) && findw(curr_joined,CU) =0
THEN curr_joined = catx(', ', curr_joined, CU);

if last.ID;

run;

Re: I have a real time scenario

mkeintz — Sun, 14 Oct 2018 21:47:08 GMT

@avishek2411: I think you have the right idea. But the data step after proc sort can be simplified greatly by have a SET and BY statement inside a DO UNTIL (LAST.ID) loop:

data temp;
input id zip cntr $;
cards;
1234 30004 USD
1234 30012 USD
1289 30056 USD
1389 45009 INR
1456 56789 INR
1456 30067 USD
1567 30099 USD
1567 67890 GBP
1567 30088 INR
1567 30092 INR
;

proc sort data=temp(drop=zip) out=temp1 nodupkey;
by id cntr;
run;
data want;
  do until (last.id);
	set temp1;
	by id;
    length cntr_list $20;
	cntr_list=catx(',',cntr_list,cntr);
  end;
  drop cntr;
run;

The only downside is that the currency list for each id is alphabetic. It is no longer in the order of appearance. Have to ask @sahaji whether that is meaningful deficiency.