Quartz | Level 8

## summary statistics

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

## Re: summary statistics

```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;
column a b;
run;

```
24 REPLIES 24
PROC Star

## Re: summary statistics

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

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

## Re: summary statistics

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

PROC Star

## Re: summary statistics

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

--------------------------
Super User

## Re: summary statistics

```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;
column a b;
run;

```
Quartz | Level 8

## Re: summary statistics

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

Super User

## Re: summary statistics

```

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;
column a b;
run;

```
Quartz | Level 8

## Re: summary statistics

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;

Super User

## Re: summary statistics

```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.

```
Quartz | Level 8

## Re: summary statistics

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;

Quartz | Level 8

## Re: summary statistics

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

Thanks

Super User

## Re: summary statistics

```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.

```
Super User

## Re: summary statistics

```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;

```
Quartz | Level 8

## Re: summary statistics

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

Super User

## Re: summary statistics

```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 ;

```
Discussion stats
• 24 replies
• 2215 views
• 7 likes
• 4 in conversation