topic Re: How to use sas code to make a table like this... (urgent！） in SAS Programming

How to use sas code to make a table like this... (urgent！）

AmberForever — Tue, 09 Dec 2014 07:07:44 GMT

Hi all,

I am new to SAS programming and have tried a lot of times try to convert the table on the left to the right one use sas code....Could anyone help me?

This is the source table. If today is Monday, then the code will run to convert the source table into the output table.The blank of the output table is the No. of student registered that week. The week start from Sunday.

Really thank you very much !

Re: How to use sas code to make a table like this... (urgent！）

LinusH — Tue, 09 Dec 2014 08:06:06 GMT

Proc tabulate

Re: How to use sas code to make a table like this... (urgent！）

Patrick — Tue, 09 Dec 2014 08:15:04 GMT

So what's the question? How to create the table in general (Proc Tabulate) or how to get the grouping into "This Week" and "Last Week" or something else?

O.K. - let's assume your problem is the week grouping. What you could do is to dynamically create a format and then use this format on source variable "date" in the Proc Tabulate.

Below the code to create the format.

data fmt_source;

format start date9.;

length label $20.;

retain fmtname 'WeekGroup';

retain type 'N';

do start=today()-100 to today();

week=intck('week',start,today());

select(week);

when(0) label='This Week';

when(1) label='Last Week';

otherwise label=catx(' ',week,'weeks ago');

end;

output;

end;

run;

proc format cntlin=fmt_source;

run;

/** and here PROC TABULATE DATA=SOURCE.... **/

/* test the format */

data _null_;

date=today();

put date WeekGroup.;

date=today()-8;

put date WeekGroup.;

date=today()-40;

put date WeekGroup.;

run;

Re: How to use sas code to make a table like this... (urgent！）

AmberForever — Tue, 09 Dec 2014 09:39:24 GMT

Hi, thanks!

My questions is how to create the table, as well as the weekly grouping....

Re: How to use sas code to make a table like this... (urgent！）

Thamaraikannan — Tue, 09 Dec 2014 13:05:34 GMT

Try this

data src_rec;

input name $ sex $ school $ dt date9.;

datalines;

AAA F Law 9-sep-14

BBB F IT 10-Aug-14

CCC M Law 19-Sep-14

DDD F Art 10-Dec-14

EEE F Law 9-sep-14

FFF M Biz 10-Aug-14

GGG F Law 9-Dec-14

HHH M IT 6-Dec-14

III M Law 19-Nov-14

JJJ M Art 10-Aug-14

kkk F Law 9-Dec-14

lll F Biz 8-Dec-14

;

run;

data date_flg ;

set src_rec;

if intck('day',(INTNX('week',dt,0,'B' )),today())>= 7 then flag = 'This week' ;

else flag = 'last week' ;

run;

proc tabulate data = date_flg;

class school sex flag;

table school*sex,flag='No of student Registered'*n='';

run;

Re: How to use sas code to make a table like this... (urgent！）

AmberForever — Wed, 10 Dec 2014 01:16:17 GMT

Hi Thamaraikannan,

Thank you so much. Can I know how to add more columns type in the table? Can I modify the code like the following?

proc tabulate data = date_flg;

class school sex flag;

table school*sex,flag='No of student Registered'*n='', flag = 'No of Student Registered 2'*n=", flag = 'No of Student Registered 3'*n=";

run;

Re: How to use sas code to make a table like this... (urgent！）

AmberForever — Wed, 10 Dec 2014 07:09:30 GMT

Thamaraikannan

Hi Tamara,

Can I know how to modify your code to have the the following table? Thank you so much! I have tried modify your codes...but not work...

Re: How to use sas code to make a table like this... (urgent！）

Thamaraikannan — Wed, 10 Dec 2014 09:47:17 GMT

data src_rec;

input name $ sex $ school $ dt date9. score;

datalines;

AAA F Law 9-sep-14 10

BBB F IT 10-Aug-14 20

CCC M Law 19-Sep-14 30

DDD F Art 10-Dec-14 25

EEE F Law 9-sep-14 40

FFF M Biz 10-Aug-14 45

GGG F Law 9-Dec-14 55

HHH M IT 6-Dec-14 50

III M Law 19-Nov-14 45

JJJ M Art 10-Aug-14 20

kkk F Law 9-Dec-14 25

lll F Biz 8-Dec-14 20

;

run;

data date_flg ;

set src_rec;

if intck('day',(INTNX('week',dt,0,'B' )),today())>= 7 then do;

flag = 'This week' ;

else do;

flag = 'last week' ;

run;

proc tabulate data = date_flg;

class school sex flag;

var score;

table (school*sex ),flag='No of student Registered'*n=''(flag='Sum of score'*score=''*sum='')(flag='Avg of score'*score=''*mean='') ;

run;

Re: How to use sas code to make a table like this... (urgent！）

Patrick — Wed, 10 Dec 2014 11:43:14 GMT

The sample data you're using illustrates why you shouldn't code "else do; flag = 'last week' ; end;" but should calculate "last week" and then eventually have an other case like "else flag='earlier weeks' "

Below a code sample based on your code but using a format instead.

data fmt_source;

format start date9.;

length label $20.;

retain fmtname 'WeekGroup';

retain type 'N';

do start=intnx('year',today(),-1) to today();

week=intck('week',start,today());

select(week);

when(0) label='This Week';

when(1) label='Last Week';

otherwise label=catx(' ',week,'weeks ago');

end;

output;

end;

run;

proc format cntlin=fmt_source;

run;

data src_rec;

input name $ sex $ school $ dt date9. score;

datalines;

AAA F Law 9-sep-14 10

BBB F IT 10-Aug-14 20

CCC M Law 19-Sep-14 30

DDD F Art 10-Dec-14 25

EEE F Law 9-sep-14 40

FFF M Biz 10-Aug-14 45

GGG F Law 9-Dec-14 55

HHH M IT 6-Dec-14 50

III M Law 19-Nov-14 45

JJJ M Art 10-Aug-14 20

kkk F Law 9-Dec-14 25

lll F Biz 8-Dec-14 20

;

run;

proc tabulate data = src_rec order=unformatted;

class school sex dt;

var score;

format dt WeekGroup.;

table (school*sex ),dt='No of student Registered'*n=''(dt='Sum of score'*score=''*sum='')(dt='Avg of score'*score=''*mean='');

run;