topic Re: List First week and Last week? in SAS Programming

List First week and Last week?

ybz12003 — Tue, 05 Dec 2023 21:07:01 GMT

Hello,

I'm looking for each state's first week and last week in the sample dataset. Please help. Thanks.

data Have;  
	length State $5 Week 3; 
	infile datalines delimiter=','; 
	input State Week;  
	datalines;                     
	GA,2101,
	GA,2102,
	GA,2104,
	GA,2105,
	GA,2106,
	GA,2109,
	GA,2110,
	GA,2115,
	GA,2117,
	GA,2118,
	GA,2120,
	GA,2123,
	GA,2124,
	NV,2201,
	NV,2202,
	NV,2204,
	NV,2205,
	NV,2206,
	NV,2209,
	NV,2210,
	NV,2213,
;   

data want_week_1st_last;
	length State $5 Week_start 3  Week_end 3; 
	infile datalines delimiter=','; 
	input State  Week_start  Week_end;  
	datalines;  
	GA, 2101,2124,
	NV, 2201,2213,
;

Re: List First week and Last week?

SASKiwi — Tue, 05 Dec 2023 21:15:50 GMT

SQL is a good way to do this:

proc sql;
  create table Want as
  select State
        ,min(Week) as Week_Start
        ,max(Week) as Week_End
  from Have
  group by State
  ;
quit;

Re: List First week and Last week?

ybz12003 — Tue, 05 Dec 2023 21:17:47 GMT

I would like to do it in the data step

Re: List First week and Last week?

Kurt_Bremser — Tue, 05 Dec 2023 21:18:10 GMT

RETAIN week_start. Use BY state. Set week_start at first.state. Use last.state as condition in a subsetting if, and set week_end there.

Re: List First week and Last week?

A_Kh — Tue, 05 Dec 2023 21:21:01 GMT

I would use first.variable and last.variable in BY-Groups.

data want;
	set have;
	by state;
	if not (first.state or last.state) then delete;
	retain first_week last_week;
	if first.state then first_week=week;
	if last.state then last_week=week;
	if last.state; 
	drop week; 
proc print; run;

Re: List First week and Last week?

SASKiwi — Tue, 05 Dec 2023 21:23:17 GMT

@ybz12003 wrote:
I would like to do it in the data step

Why?

Re: List First week and Last week?

ballardw — Tue, 05 Dec 2023 21:39:31 GMT

Proc summary:

proc summary data=have nway;
   class state;
   var week;
   output out=want (drop=_:) min(week)=week_start max(week)=week_end;
run;

Some possible reasons to use proc summary/means:

If you want to know how many weeks are represented don't drop the _freq_ variable that is normally included.

If of interest much easier to add a "mean week" or median.

If the data has observations missing week then asking for NMISS stat . Adding all that to a data step would be ugly.

If the desired output is just to see then use a report procedure.

proc tabulate data=have f=best5.;
   class state;
   var week;
   table state,
           week=' '*(min='Week start' max='Week end')
   ;
run;

Re: List First week and Last week?

Kurt_Bremser — Wed, 06 Dec 2023 06:45:04 GMT

@A_Kh you can get the result with less coding:

data want;
retain state first_week end_week;
set have (rename=(week=end_week9);
by state;
if first.state then first_week = end_week;
if last.state;
run;