DATA Step, Macro, Functions and more

How to use sas code to make a table like this... (urgent!)

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

How to use sas code to make a table like this... (urgent!)

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.

Source Table.jpg        Output Table.jpg

Really thank you very much !


Accepted Solutions
Solution
‎12-09-2014 08:05 AM
New Contributor
Posts: 3

Re: How to use sas code to make a table like this... (urgent!)

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;

output.PNG

View solution in original post


All Replies
Super User
Posts: 5,256

Re: How to use sas code to make a table like this... (urgent!)

Proc tabulate

Data never sleeps
Respected Advisor
Posts: 3,887

Re: How to use sas code to make a table like this... (urgent!)

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;

New Contributor
Posts: 4

Re: How to use sas code to make a table like this... (urgent!)

Hi, thanks!

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

Solution
‎12-09-2014 08:05 AM
New Contributor
Posts: 3

Re: How to use sas code to make a table like this... (urgent!)

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;

output.PNG

New Contributor
Posts: 4

Re: How to use sas code to make a table like this... (urgent!)

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;

New Contributor
Posts: 4

Re: How to use sas code to make a table like this... (urgent!)

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

source tbale.png

output file.png

New Contributor
Posts: 3

Re: How to use sas code to make a table like this... (urgent!)

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;

output.PNG

Respected Advisor
Posts: 3,887

Re: How to use sas code to make a table like this... (urgent!)

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;

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 419 views
  • 8 likes
  • 4 in conversation