Hello Everyone,
I was wondering if someone might know how to do this because unfortunately I do not have an example to go from in my notes.
What I have is a dataset with a bunch of Customer IDs and their countries which I need to turn into something that looks like this:
Customer Customer
Country List
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
AU 29, 41, 53, 111, 171, 183, 195, 215
If there is anyone out there who would know how to use hash objects to concatenate a list like this together, it would be greatly appreciated!
Thanks!
Alisa
Hi,
I don't why you have to use hash(), since there are better and established approaches out there without involving hash(), but I guess it is the special requirement of your homework. Try this:
data have;
input country$ id$;
cards;
au 1
au 2
usa 3
usa 4
usa 5
;
data want (keep=country new_id);
length new_id $50.;
if _n_=1 then do;
set have(obs=1);
dcl hash h1(dataset:'have', multidata:'y');
h1.definekey('country');
h1.definedata(all:'y');
h1.definedone();
dcl hash h2(dataset:'have');
h2.definekey('country');
h2.definedone();
dcl hiter h2i('h2');
_rc=h2i.first();
end;
do while (_rc=0);
do _rc=h1.find() by 0 while (_rc=0);
new_id=catx(',',new_id,id);
_rc=h1.find_next();
end;
output;
call missing (new_id);
_rc=h2i.next();
end;
run;
Haikuo
Hi,
I don't why you have to use hash(), since there are better and established approaches out there without involving hash(), but I guess it is the special requirement of your homework. Try this:
data have;
input country$ id$;
cards;
au 1
au 2
usa 3
usa 4
usa 5
;
data want (keep=country new_id);
length new_id $50.;
if _n_=1 then do;
set have(obs=1);
dcl hash h1(dataset:'have', multidata:'y');
h1.definekey('country');
h1.definedata(all:'y');
h1.definedone();
dcl hash h2(dataset:'have');
h2.definekey('country');
h2.definedone();
dcl hiter h2i('h2');
_rc=h2i.first();
end;
do while (_rc=0);
do _rc=h1.find() by 0 while (_rc=0);
new_id=catx(',',new_id,id);
_rc=h1.find_next();
end;
output;
call missing (new_id);
_rc=h2i.next();
end;
run;
Haikuo
Hi Hai.kuo,
So I have adapted your script into what I need, but it still isn't quite working.
Could you let me know where I am going wrong?
data customer_list (keep=Country All_Customers);
length All_Customers $200.;
if _n_=1 then do;
dcl hash h1(dataset:'orion.customer', multidata:'y');
h1.definekey('Country');
h1.definedata('Customer_ID');
h1.definedone();
dcl hash h2(dataset:'orion.customer');
h2.definekey('Country');
h2.definedone();
dcl hiter h2i('h2');
_rc=h2i.first();
end;
do while (_rc=0);
do _rc=h1.find() by 0 while (_rc=0);
new_id=catx(',',All_Customers,Customer_ID);
_rc=h1.find_next();
end;
output;
call missing (All_Customers, Customer_ID);
_rc=h2i.next();
end;
set orion.customer /*(obs=1)*/;
run;
proc print data=customer_list;
run;
My log looks normal:
NOTE: There were 77 observations read from the data set ORION.CUSTOMER.
NOTE: There were 77 observations read from the data set ORION.CUSTOMER.
NOTE: There were 77 observations read from the data set ORION.CUSTOMER.
NOTE: The data set WORK.CUSTOMER_LIST has 7 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.06 seconds
cpu time 0.04 seconds
Thanks!
Alisa
Hi Hai.kuo,
I figured out where my issue was.
Everything works fine now!
Thank you so much for your help!
Alisa
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.