variable date to week

[jaiganesh]

Hello,

I've below input:

Obs route   _NAME_ D_11JUN2019 D_12JUN2019 D_22JUN2018 D_15MAY2019 D_01SEP2019 D_10MAY2019 D_01FEB2019 
1   Route1   count                   4                 17                          .                       .                        .                        .                      .
2   Route2   count                    .                 .                            4                     17                        .                        .                       .
3   Route3   count                   .                  .                           .                      .                          6                       17                      . .
4  Route4   count                    .                  .                             .                       .                        .                        .                         5
5   Route6   count                    .                  .                            .                        .                       .                       .                           1

and expecting output with respect to week wise , If date is following in same week then value should be added like below Week24_2019 and need to look for the year as well.

Obs route _NAME_ WEEK24_2019  WEEK25_2018 WEEK20_2019 WEEK35_2019 WEEK19_2019 WEEK4_2019 WEEK5_2019
1 Route1   count                21                       .                           .                      .                             .                       .                       .
2 Route2   count                 .                       4                          17                        .                        .                       ..
3 Route3   count                 .                        .                           .                          6                     17                       .
4 Route4   count                 .                         .                        .                           .                          .                        .                        5.
5 Route6   count                 .                         .                        .                           .                           .                                                1

Thanks in advance.

[heffo]

It seems like you have done a transpose, could you before the previous step take the date but format it with weeku6 (or weekv6. or weekw6. depending on your preferences). You will also have to summarise it grouped on route and the variable that the date was in. 

[jaiganesh]

Thank for the response. This was the code written before Transpose.

data a;
input route $ count region date9.;
datalines;
Route1 17 12Jun2018
Route2 4 13Jun2018
Route3 17 22Jun2019
Route4 5 01Feb2019
Route3 6 01Sep2019
Route6 1 08Sep2019
Route2 17 15May2019
Route1 4 11Jun2018

;
run;

proc sort data=a out=b nodupkey;
by route count;
run;

//below i used weekv6. format

proc print data=b;
format region weekv6.;  /
run;

Now i need to do grouping of it , hence tried with below code , However does not work, could you please suggest, How i can do it.

proc sql;
select route, sum(count) as Count , region format=weekv6. from b group by route , region;
run;

[Kurt_Bremser]

It does work, and no prior sorting necessary:

data a;
input route $ count region date9.;
datalines;
Route1 17 12Jun2018
Route2 4 13Jun2018
Route3 17 22Jun2019
Route4 5 01Feb2019
Route3 6 01Sep2019
Route6 1 08Sep2019
Route2 17 15May2019
Route1 4 11Jun2018
;

proc sql;
create table c as
select
  route,
  sum(count) as Count ,
  region format=weekv6.
from a
group by route , region;
run;

proc print data=c noobs;
run;

Result:

route     Count    region

Route1       4     18W24 
Route1      17     18W24 
Route2       4     18W24 
Route2      17     19W20 
Route3      17     19W25 
Route3       6     19W35 
Route4       5     19W05 
Route6       1     19W36 

BIG HINT: "does not work" on its own is totally useless (and I consider it worthy of the proverbial blonde secretary as depicted in bad movies). ALWAYS(!) state in detail where results did not meet your expectations, and post the log of steps you consider failed. ALWAYS.

[jaiganesh]

Hello Sir,

Thanks for response.

My requirement later on is to transpose  REGION column to variables, So i need to have unique variables/columns, Hence looking here to have the grouping with Route and Region wise.

then Result should be the Addition of Count, So first observation appears to be like below:

Route1       21     18W24 

The Query is really not doing the grouping ?

proc sql;
create table c as
select
  route,
  sum(count) as Count ,
  region format=weekv6.
from a
group by route , region;
run;

 Please suggest.

[Kurt_Bremser]

Then you need to create a new region with the week value:

data a;
input route $ count region date9.;
datalines;
Route1 17 12Jun2018
Route2 4 13Jun2018
Route3 17 22Jun2019
Route4 5 01Feb2019
Route3 6 01Sep2019
Route6 1 08Sep2019
Route2 17 15May2019
Route1 4 11Jun2018
;

proc sql;
create table c as
select
  route,
  sum(count) as Count ,
  put(region,weekv6.) as region
from a
group by route, calculated region;
run;

proc print data=c noobs;
run;

proc transpose data=c out=final prefix=region_;
by route;
id region;
var count;
run;

proc print data=final noobs;
run;

Results:

route     Count    region

Route1      21     18W24 
Route2       4     18W24 
Route2      17     19W20 
Route3      17     19W25 
Route3       6     19W35 
Route4       5     19W05 
Route6       1     19W36 

and

                    region_    region_    region_    region_    region_    region_
route     _NAME_     18W24      19W20      19W25      19W35      19W05      19W36

Route1    Count        21          .          .         .          .          .   
Route2    Count         4         17          .         .          .          .   
Route3    Count         .          .         17         6          .          .   
Route4    Count         .          .          .         .          5          .   
Route6    Count         .          .          .         .          .          1   

[jaiganesh]

Thanks for Response.

I would like to know, How can i arrange the Column name of Week in ascending order while doing the Transpose .

Columns of weeks should come one after other in ascending order,

I tried to arrange them in ascending order before Transpose, However Variables written in the BY(in Transpose) doesn't agree on it. Week can not be arrange in the ascending order before the Transpose.

Could you please suggest on this please.

[Kurt_Bremser · Accepted solution]

Add steps to retrieve the column names in order, and to set this order:

data have;
input route $ count region date9.;
datalines;
Route1 17 12Jun2018
Route2 4 13Jun2018
Route3 17 22Jun2019
Route4 5 01Feb2019
Route3 6 01Sep2019
Route6 1 08Sep2019
Route2 17 15May2019
Route1 4 11Jun2018
;

proc sql;
create table sum as
select
  route,
  sum(count) as count,
  put(region,weekv6.) as region
from have
group by route, calculated region;
run;

proc transpose
  data=sum
  out=trans (drop=_name_)
  prefix=region_
;
by route;
id region;
var count;
run;

proc sql noprint;
select name into :varnames separated by " "
from dictionary.columns
where libname = 'WORK' and memname = 'TRANS' and name like 'region%'
order by name
;
quit;

data want;
retain route &varnames;
set trans;
run;

proc print data=want noobs;
run;

Result:

          region_    region_    region_    region_    region_    region_
route      18W24      19W05      19W20      19W25      19W35      19W36

Route1       21         .           .          .         .          .   
Route2        4         .          17          .         .          .   
Route3        .         .           .         17         6          .   
Route4        .         5           .          .         .          .   
Route6        .         .           .          .         .          1   

[jaiganesh]

This really works to me, I got one more concern here, How about pulling only next 52 Weeks Data.

Current Week should be consider as the WEEK 1 (June3-June9), WEEK2 (June10-June16) and WEEK3(June17-June23) So on........

So only next 52 Week should shown in output from current week (Monday-Sunday consider as week), Therefore date's falls in only next 52 Weeks should be shown with respect to WEEK numbers (from Week1-Week52)

Could you please suggest here.......

[Reeza]

Weeks can go from 1 to 53 or 0 to 52. 365/7 = 52.143 so you'll always have a few days outside the 52 week range.

[jaiganesh]

Got it , 53 Week should be consider.

If you please suggest the code for the same.

[Kurt_Bremser]

This is done with a simple where condition that subsets the data to a given period.

[jaiganesh]

Along with this, The Dates are arrange week wise for only next 53 Week including current week , As current week should be consider as WEEK1(Monday-Sunday).

I tried something below code to figure out next 53 weeks, 

options symbolgen mprint mlogic;
%macro Week;
data m1;
%do i=0 %to 52;
SHPDT=today();
put SHPDT=;
WeekStart=intnx('week', SHPDT, &i,'b')+1;
WeekEnd=intnx('week', SHPDT, &i,'e')+1;

format SHPDT WeekStart WeekEnd date9.;

drop SHPDT;
output;
%end;
run;
%mend week;

If you could suggest coding approach to integrate the existing code with this requirement.

[jaiganesh]

Thanks you so much for your prompt response, You been very helpful throughout the investigation.

[Reeza]

Use PROC TRANSPOSE to transpose your data to a wide format.

Use the WEEK function or format to convert your dates to weeks. Then use PROC MEANS to sum the data to have everything in the same week grouped.

Then transpose it back to a wide format so that you have the format you want. But this format is very hard to work with as is, so you may want to consider sticking with a long format.