Help using Base SAS procedures

I have a real time scenario

Reply
Occasional Contributor
Posts: 15

I have a real time scenario

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

Respected Advisor
Posts: 3,124

Re: I have a real time scenario

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

Occasional Contributor
Posts: 15

Re: I have a real time scenario

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

Occasional Contributor
Posts: 15

Re: I have a real time scenario

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;

Respected Advisor
Posts: 3,124

Re: I have a real time scenario

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

Occasional Contributor
Posts: 15

Re: I have a real time scenario

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;

Respected Advisor
Posts: 4,663

Re: I have a real time scenario

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;

PG

PG
New Contributor
Posts: 2

Re: I have a real time scenario

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;

Ask a Question
Discussion stats
  • 7 replies
  • 1597 views
  • 3 likes
  • 5 in conversation