BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AmberForever
Calcite | Level 5

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
Thamaraikannan
Fluorite | Level 6

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

8 REPLIES 8
LinusH
Tourmaline | Level 20

Proc tabulate

Data never sleeps
Patrick
Opal | Level 21

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;

AmberForever
Calcite | Level 5

Hi, thanks!

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

Thamaraikannan
Fluorite | Level 6

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

AmberForever
Calcite | Level 5

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;

AmberForever
Calcite | Level 5

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

Thamaraikannan
Fluorite | Level 6

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

Patrick
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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