Help using Base SAS procedures

sql data summary by id and year

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

sql data summary by id and year

dear experts,

I have data in the following format:

idupc_codeyear
200035112300704132004
2000351.2004
200035112300000182004
200035112300704992004
2000351.2004
2000351.2005
200035112300118132005
2000351.2005
2000351.2005
2000351.2005
200035112300704132005
2000351.2005
2000351.2005
2000351.2005
2000351.2005
200035112300118132005


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

idyearcount
200035120043
200035120053


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,

c


Accepted Solutions
Solution
‎08-16-2011 08:05 PM
PROC Star
Posts: 7,356

Re: sql data summary by id and year

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;

View solution in original post


All Replies
Contributor
Posts: 44

Re: sql data summary by id and year

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;

Solution
‎08-16-2011 08:05 PM
PROC Star
Posts: 7,356

Re: sql data summary by id and year

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;

Contributor
Posts: 44

Re: sql data summary by id and year

thank you very much!

Super User
Posts: 9,662

Re: sql data summary by id and year

Or

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

Trusted Advisor
Posts: 1,300

sql data summary by id and year

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

or

proc means data=temp n noprint;

var upc_code;

by id year;

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

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 149 views
  • 1 like
  • 4 in conversation