topic sql data summary by id and year in SAS Procedures

sql data summary by id and year

jkf91 — Tue, 16 Aug 2011 23:51:11 GMT

dear experts,

I have data in the following format:

id	upc_code	year
2000351	1230070413	2004
2000351	.	2004
2000351	1230000018	2004
2000351	1230070499	2004
2000351	.	2004
2000351	.	2005
2000351	1230011813	2005
2000351	.	2005
2000351	.	2005
2000351	.	2005
2000351	1230070413	2005
2000351	.	2005
2000351	.	2005
2000351	.	2005
2000351	.	2005
2000351	1230011813	2005

I want to create something like this using sql for each id number:

id	year	count
2000351	2004	3
2000351	2005	3

count counts each nonempty upc_code as 1.

can someone please tell me how this can be done? it's my first time using sas to make data summary such as this.

many thanks,

Re: sql data summary by id and year

jkf91 — Tue, 16 Aug 2011 23:53:19 GMT

FYI: I tried something like this.

proc sql;

create table my_count as

select *, sum(yes_purchase) as num_purchase_year

(select id, (upc_code ne .) as yes_purchase)

group by year;

quit;

Re: sql data summary by id and year

art297 — Wed, 17 Aug 2011 00:05:28 GMT

How about if you try something like:

proc sql;

create table my_count as

select id,sum(not missing(upc_code)) as

num_purchase_year, year

from have

group by id,year

;

quit;

Re: sql data summary by id and year

jkf91 — Wed, 17 Aug 2011 00:33:46 GMT

thank you very much!

Re: sql data summary by id and year

Ksharp — Wed, 17 Aug 2011 08:44:31 GMT

data temp;
input id     upc_code      year ;
cards;
2000351     1230070413     2004
2000351     .     2004
2000351     1230000018     2004
2000351     1230070499     2004
2000351     .     2004
2000351     .     2005
2000351     1230011813     2005
2000351     .     2005
2000351     .     2005
2000351     .     2005
2000351     1230070413     2005
2000351     .     2005
2000351     .     2005
2000351     .     2005
2000351     .     2005
2000351     1230011813     2005
;
run;
proc sql noprint;
 create table want as 
  select id,year,count(*) as freq
   from temp
    where upc_code is not missing
     group by id,year;
quit;

Ksharp

sql data summary by id and year

FriedEgg — Wed, 17 Aug 2011 18:36:58 GMT

data temp;

input id upc_code year ;

cards;

2000351 1230070413 2004

2000351 . 2004

2000351 1230000018 2004

2000351 1230070499 2004

2000351 . 2004

2000351 . 2005

2000351 1230011813 2005

2000351 . 2005

2000351 1230070413 2005

2000351 . 2005

2000351 1230011813 2005

;

run;

proc format;

value upcfmt

0-high='1'

other='0';

run;

proc freq data=temp;

tables upc_code /out=want(drop=upc_code percent) noprint;

by id year;

where upc_code > 0;

format upc_code upcfmt;

run;

proc means data=temp n noprint;

var upc_code;

by id year;

output out=want(drop=_type_ _freq_) n=count;

run;