Hi ,
I have a dataset xyz like below which has customer id and pricing attributes . I want to check how many ids are assigned 200 of pricing 01
the report should be like for example and can i write a loop for this ?
cnt_id | pricing01 |
3 | 200 |
1 | 100 |
1 | 150 |
cnt_id | pricing02 |
4 | 300 |
1 | 400 |
cnt_id | pricing03 |
2 | 240 |
1 | 626 |
1 | 721 |
1 | 666 |
customerid | pricing01 | pricing02 | pricing03 |
12345 | 200 | 300 | 240 |
12345 | 200 | 300 | 240 |
123 | 100 | 300 | 626 |
121 | 200 | 300 | 721 |
126 | 150 | 400 | 666 |
How about this one. data have; infile cards truncover expandtabs; input customerid pricing01 pricing02 pricing03; cards; 12345 200 300 240 12345 200 300 240 123 100 300 626 121 200 300 721 126 150 400 666 ; run; proc sql; create table want as select 1 as x,'cnt_id' as a length=12,'pricing01' as b length=12 from have(obs=1) union select 2,put(count(customerid),best8. -l) , put(pricing01,best8. -l) from have group by pricing01 union select 3,' ',' ' from have(obs=1) union select 4 as x,'cnt_id' as a length=12,'pricing02' as b length=12 from have(obs=1) union select 5,put(count(customerid),best8. -l) , put(pricing02,best8. -l) from have group by pricing02 union select 6,' ',' ' from have(obs=1) union select 7 as x,'cnt_id' as a length=12,'pricing03' as b length=12 from have(obs=1) union select 8,put(count(customerid),best8. -l) , put(pricing03,best8. -l) from have group by pricing03 ; quit; proc report data=want nowd noheader; column a b; run;
Do you require the non-sorted order of pricing values within each pricing group? It appears you want the same order as the values are first encountered, right?
hi
Any order of pricing values should be fine but i need how many ids when pricing01 is 200 and how many i have when pricing01 is 100 and how maany ids i have when pricing01 is 150 same way for pricing02
Thanks
Let PROC FREQ do the work. This program has "ORDER=DATA" which present counts in order that the values appear. Take it out if you want ascending order for each variable.
Notes:
regards,
Mark
data have;
input customerid pricing01 pricing02 pricing03 ;
datalines;
12345 200 300 240
12345 200 300 240
123 100 300 626
121 200 300 721
126 150 400 666
run;
proc freq data=have order=data noprint;
table pricing01 / out=priceing01 ;
table pricing02 / out=priceing02 ;
table pricing03 / out=priceing03 ;
run;
data _null_;
set priceing: indsname=inw;
array prc{*} pricing: ;
if inw^=lag(inw) then do;
p+1;
vnam=vname(prc{p});
put 'CNT_ID ' vnam;
end;
put count 6. +3 prc{p} 3.;
run;
How about this one. data have; infile cards truncover expandtabs; input customerid pricing01 pricing02 pricing03; cards; 12345 200 300 240 12345 200 300 240 123 100 300 626 121 200 300 721 126 150 400 666 ; run; proc sql; create table want as select 1 as x,'cnt_id' as a length=12,'pricing01' as b length=12 from have(obs=1) union select 2,put(count(customerid),best8. -l) , put(pricing01,best8. -l) from have group by pricing01 union select 3,' ',' ' from have(obs=1) union select 4 as x,'cnt_id' as a length=12,'pricing02' as b length=12 from have(obs=1) union select 5,put(count(customerid),best8. -l) , put(pricing02,best8. -l) from have group by pricing02 union select 6,' ',' ' from have(obs=1) union select 7 as x,'cnt_id' as a length=12,'pricing03' as b length=12 from have(obs=1) union select 8,put(count(customerid),best8. -l) , put(pricing03,best8. -l) from have group by pricing03 ; quit; proc report data=want nowd noheader; column a b; run;
Hi Ksharp,
Can I get something easy code like
Proc sql;
create table xy as
select count(customerid),
pricing01
from xyz
group by pricing01
;
quit;
but I have 100's of variables so I want this to be in macro so that each time when it loops it would create a table for each pricing item
and can we use proc summary for the report to look in the format the mentioned
Thanks
Thanks
data have; infile cards truncover expandtabs; input customerid pricing01 pricing02 pricing03; cards; 12345 200 300 240 12345 200 300 240 123 100 300 626 121 200 300 721 126 150 400 666 ; run; proc transpose data=have(keep=pricing: obs=0) out=temp; run; data temp; set temp; retain n -2; n=n+3; run; data _null_; set temp end=last; if _n_=1 then call execute('proc sql;create table want as '); call execute(cat('select ',n,' as x,"cnt_id" as a length=12,"', _name_,'" as b length=12 from have(obs=1) union select ',n+1, ',put(count(customerid),best8. -l),put(',_name_,',best8. -l) from have group by ', _name_)); if not last then call execute(cat('union select ',n+2,'," "," " from have(obs=1) union')); else call execute(';quit;'); run; proc report data=want nowd noheader; column a b; run;
Hi Ksharp,
The code worked perfectly but it works only when i have few records in 1000 but when i have 2 lakh records in the data set have
the below step is taking very long time is there any other other code so that it takes very less time Can u please help
data _null_;
set temp end=last;
if _n_=1 then call execute('proc sql;create table want as ');
call execute(cat('select ',n,' as x,"cnt_id" as a length=12,"',
_name_,'" as b length=12 from have(obs=1) union select ',n+1,
',put(count(customerid),best8. -l),put(',_name_,',best8. -l) from have group by ',
_name_));
if not last then call execute(cat('union select ',n+2,'," "," " from have(obs=1) union'));
else call execute(';quit;');
run;
Thanks for all your help
Sorry. I think time is wasted on PUT(). Do you have to make a table contain these, which make variable have to be character type. Can't you just create a report to get it ?Like: proc sql; select count(*) as cust_id,price_01 from have group by price_01 union all select count(*) as cust_id,price_02 from have group by price_02 .................................. Another way is using proc means/proc freq , But that need make a macro firstly and need more code too. Or IML code if your memory is big enough.
Hi Ksharp,
Its ok if the code is big and no IML and i dont need to make my variables character and we can remove put from the code
I tried to remove put and run it but I am getting the following error with below code
+ proc sql;
1 + create table test77 as
NOTE: Line generated by the CALL EXECUTE routine.
2 + select 1 as x,"cnt_id" as a length=12,"Pricing01 " as b length=100 from test7(obs=1) union select
2 !+2,count(customerid),_name_, from test7 group by pricing01
_____
22
76
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 76-322: Syntax error, statement will be ignored.
Code I have used
data _null_;
set temp end=last;
if _n_=1 then call execute('proc sql;create table want as ');
call execute(cat('select ',n,' as x," cnt_id" as a length=12,"',
_name_,'" as b length=100 from have(obs=1) union select ',n+1,
',count(customerid),_name_, from have group by ',
_name_));
if not last then call execute(cat('union select ',n+2,'," "," " from have (obs=1) union'));
else call execute(';quit;');
run;
Thanks for your help
Hi Ksharp ,
Is there any possibility that you could remove put and then send code again because i tried to remove it but i am getting syntax errors for data_null_ step
Can u please help
Thanks
The question is do you have to make a table ? that would lead to have to use PUT() which would be very slowly. Or just want make a report as Cynthia did, that would be very fast.
The following just generated a report ,should be very fast. data have; infile cards truncover expandtabs; input customerid pricing01 pricing02 pricing03; cards; 12345 200 300 240 12345 200 300 240 123 100 300 626 121 200 300 721 126 150 400 666 ; run; proc transpose data=have(keep=pricing: obs=0) out=temp; run; data _null_; set temp end=last; if _n_=1 then call execute('proc sql; '); call execute(cat('select count(customerid) as cust_id,',_name_, ' from have group by ',_name_,';')); if last then call execute(';quit;'); run;
Hi Ksharp,
yes but the code doesnot union and create all the combinations in a single dataset like your code before same excatly like below but i dont need any put as it will take lot of time . Cynthia's report also i have ran it but i need a dataset instead of report
cnt_id | pricing01 |
3 | 200 |
1 | 100 |
1 | 150 |
cnt_id | pricing02 |
4 | 300 |
1 | 400 |
cnt_id | pricing03 |
2 | 240 |
1 | 626 |
1 | 721 |
1 | 666 |
Thanks for all help
Then you have no choice. you have to use PUT() , no matter what method I used in SQL or DataStep ,HashTable, IML .... Add the following maybe a faster. options bulknum=100 bulksize=128k ;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: