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

Hi, All,

I am creating some 2-month rolling sampling windows with array.

Right now,the result data set (want) has variables G1-G12 created from array.

Is there anyway I can have G01, G02, ... instead of G1, G2,...?

Thanks.

Joe

/**/

data have;

input year_mth @@;

cards;

201412 201411 201410 201409 201408 201407 201406

201405 201404 201403 201402 201401 201312 201311

201310 201309 201308 201307 201306 201305 201304

201303 201302 201301 201212 201211 201210 201209

201208 201207 201206 201205 201204 201203 201202

;

run;

%macro new;

proc sql;

select count (distinct year_mth) into:numofbatch

from have

;quit;

*determine how many lagged group I can have;

%let end=%sysfunc(floor ((&numofbatch-12)/2));

%let end2=%eval(&end+1);

*Create rolling sample windows;

data want;

set have;

G1= (_n_ <=12);

array G (&end2);

do n=1 to &end;

G(n+1)= lag2(G(n));

end;

run;

%mend;

%new

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Apply a Z2. format to the result of your caluculations

%let end = %sysfunc(putn(%sysfunc(floor ((&numofbatch-12)/2)),z2.)); if I didn't miscount ()

array g g01 - g&end;

You probably should have something to check the value of NumOfBatch as bad things are likely to happen if is ever 12 or smaller.

View solution in original post

10 REPLIES 10
ballardw
Super User

Apply a Z2. format to the result of your caluculations

%let end = %sysfunc(putn(%sysfunc(floor ((&numofbatch-12)/2)),z2.)); if I didn't miscount ()

array g g01 - g&end;

You probably should have something to check the value of NumOfBatch as bad things are likely to happen if is ever 12 or smaller.

PGStats
Opal | Level 21

SAS is pretty good at that... Try running:

data _null_;

array v{*} v_01-v_12;

do i = 1 to dim(v);

    varName = vname(v{i});

    put i varName;

    end;

run;

PG

PG
Reeza
Super User

You have the solution to your problem above via @ballardw but I can say that usually for rolling calculations you don't need to go into macros. 

jiangmi
Calcite | Level 5

Hi, Reeza,

What do you mean by saying 'usually for rolling calculations you don't need to go into macros'? Do you have an example? I'd like to see if it can simplify my process.

In my case, I need to create the rolling windows and then do certain calculation for each window using

PROC NPAR1WAY.

Thanks.

Joe

jiangmi
Calcite | Level 5

It is still helpful.

Thanks.

PGStats
Opal | Level 21

Say you wanted to compare the first and last six months in a non parametric way for variable x in every window. You could proceed like this, without macros or transpose :

data have;

input year_mth @@;

x = rand("NORMAL"); /* Variable to analyse */

datalines;

201412 201411 201410 201409 201408 201407 201406

201405 201404 201403 201402 201401 201312 201311

201310 201309 201308 201307 201306 201305 201304

201303 201302 201301 201212 201211 201210 201209

201208 201207 201206 201205 201204 201203 201202

;

data assign;

set have nobs=nobs;

lastGrp = _n_ - mod(_n_-1, 2);

do i = -12 to 2 by 2;

    grp = lastGrp + i + 2;

    if grp >= 1 and grp <= nobs - 12 then do;

        grpId = ceil(grp/2);

        output;

        end;

    end;

drop grp lastGrp i;

run;

proc sort data=assign; by grpId descending year_mth; run;

/* Create variable yr_half = 0: first six months, = 1: last six months */

proc rank data=assign out=want groups=2;

by grpId;

var year_mth; ranks yr_half;

run;

/* Rolling nonparametric comparison of the first and last six months */

proc npar1way data=want noprint;

by grpId;

class yr_half;

var x;

output out=stats wilcoxon;

run;

PG

PG
jiangmi
Calcite | Level 5

Thanks PG for your extra help.

I see your point.

I may use it later.

Thanks.

Joe

PGStats
Opal | Level 21

You could avoid using macros altogether, like this:

data assign;

set have nobs=nobs;

dum = 1;

lastGrp = _n_ - mod(_n_-1, 2);

do i = -12 to 2 by 2;

    grp = lastGrp + i + 2;

    if grp >= 1 and grp <= nobs - 12 then do;

        grpId = ceil(grp/2);

        output;

        end;

    end;

drop grp lastGrp i;

run;

proc transpose data=assign out=want(drop=_:) prefix=G;

by descending year_mth;

format grpId z2.0;

var dum;

id grpId;

run;

Depending on your needs, dataset assign might turn out to be more useful than want.

PG

PG
Ksharp
Super User

data x;

retain G1-G12 1;

run;

data y;

set x(rename=(G1-G12=G01-G12));

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
  • 10 replies
  • 1168 views
  • 6 likes
  • 5 in conversation