BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chennupriya
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;


View solution in original post

24 REPLIES 24
mkeintz
PROC Star

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?

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
chennupriya
Quartz | Level 8

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

mkeintz
PROC Star

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:

  1. If you want results to go somewhere other than the log, put a FILE statement in the data step.
  2. You might to drop the "NOPRINT" option to see what proc freq prints directly.

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;

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Ksharp
Super User
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;


chennupriya
Quartz | Level 8

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

Ksharp
Super User



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;


chennupriya
Quartz | Level 8

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

Ksharp
Super User
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.


chennupriya
Quartz | Level 8

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

chennupriya
Quartz | Level 8

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

Ksharp
Super User
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.

Ksharp
Super User
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;

chennupriya
Quartz | Level 8

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

Ksharp
Super User
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 ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 24 replies
  • 1766 views
  • 7 likes
  • 4 in conversation