DATA Step, Macro, Functions and more

Do loop to create multiple variables in single data step

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Do loop to create multiple variables in single data step

Hi , i want to create 2 variables in same data step.

One containing iteration of 1 to 3 and other storing iteration of 51-53.

and i want to do it by a key variable ID.

 

The data is like this:

 ID

101

102

103

 

The desired output:

101  1  51

101  2  52

101  3  53

102  1  51

102  2  52

102  2  53

103  1  51

103  2  52

103  3  53

 

I did it for each with do loop and merged, but looking for way to do in one step. Thanks!


Accepted Solutions
Solution
a month ago
Occasional Contributor
Posts: 8

Re: Do loop to create multiple variables in single data step

Have I got this logic right?

 

You currently have just a list of names. You want to expand that so that for each name you have one observation for each week of the year, with a variable for the week number, and a variable for the start date of that week where weeks start on a Sunday.

 

If that is correct then the below should get you what you want. It will output 52 or 53 observations per name, depending on how close Sunday is to the start of the year.

 

data have;
informat adjuster $20.;
input adjuster;
datalines;
PAUL
KATHRYN
EMMA
VICTOR
SAM
;run;

data want;
set have;
format StartDt date9.;
drop EndDt; /* This is just used to check if we've entered enough weeks to cover the year */
Week= 1;
EndDt= intnx('year',date(),0,'E'); /* End of current year */
StartDt= intnx('year',EndDt,-1,'E'); /* End of last year */
StartDt= intnx('week',StartDt,1,'B'); /* First Sunday of current year */
do while (StartDt <= EndDt);
	output; /* We started with values for week 1 so output a record immediately */
	Week= Week+1; /* Increment the week counter by 1 */
	StartDt= StartDt+7; /* A week is always 7 days long so increment date by 7 */
	/* Another loop will start as long as StartDt is still in the current year */
end;
run;

 

The code assumes week 1 starts on the first Sunday of the year, with any days before that belonging to the last week of the previous year.

View solution in original post


All Replies
Super User
Posts: 23,244

Re: Do loop to create multiple variables in single data step

Posted in reply to vpgodbole
data want;

set sashelp.class; *source data;

    do Col2 = 1 to 3;

        Col3 = 50 + Col2;
        output;

    end;

run;

Key idea is the explicit OUTPUT statement that controls the output and prints the multiple records.


@vpgodbole wrote:

Hi , i want to create 2 variables in same data step.

One containing iteration of 1 to 3 and other storing iteration of 51-53.

and i want to do it by a key variable ID.

 

The data is like this:

 ID

101

102

103

 

The desired output:

101  1  51

101  2  52

101  3  53

102  1  51

102  2  52

102  2  53

103  1  51

103  2  52

103  3  53

 

I did it for each with do loop and merged, but looking for way to do in one step. Thanks!


 

Occasional Contributor
Posts: 9

Re: Do loop to create multiple variables in single data step

Thank you all for the comments:

 

I have names of adjusters and want to assign week 1 to 52 for all and another variable in the same data step assigning start date for each week. Start date of 1st week of year goes by -  sum(intnx('week','01JAN2018'd,1),-7)  to  sum(intnx('week','31DEC2018'd,1),-7)  by  7.  That way I have the corresponding start date of each week. 

 

In am not able to bring this in 1 step.

 

Output should look like:

ADJUSTER    WEEK   Start Dt

PAUL             1            31DEC2018

PAUL             2            07JAN2018

...

PAUL             53          30DEC2018

KATHRYN     1            31DEC2018

KATHRYN     2            07JAN2018

...

KATHRYN     53          30DEC2018 

 

Thank you!!

Varun

  

PROC Star
Posts: 1,561

Re: Do loop to create multiple variables in single data step

Posted in reply to vpgodbole

Can you please post a clear sample of what you have and what you want and the logic in plain english(not intnx code) so that it is convenient for respondents to think through and offer you various solutions

Occasional Contributor
Posts: 9

Re: Do loop to create multiple variables in single data step

Posted in reply to novinosrin

My apologies. Sure, all I have with me is list of names of adjusters which I got by nodupkey on adjuster column in dataset.

PAUL

KATHRYN

EMMA

VICTOR

SAM

 

and I want to create 2 variables 1 storing week number and another storing start date of the week. This is a dummy dataset i am using to merge with the adjuster claim counts I have. So the weeks where adjuster doesn't have any data the count will be zero. 

 

Output should look like:

ADJUSTER    WEEK   Start Dt

PAUL             1            31DEC2018

PAUL             2            07JAN2018

...

PAUL             53          30DEC2018

KATHRYN     1            31DEC2018

KATHRYN     2            07JAN2018

...

KATHRYN     53          30DEC2018 

Super User
Posts: 23,244

Re: Do loop to create multiple variables in single data step

Posted in reply to vpgodbole

1. Find the start of the year

2. Use INTNX to increment that to the Sunday

3. Use a DO loop to increment through to 52/53 and add 7 to the date each time. 

 


@vpgodbole wrote:

My apologies. Sure, all I have with me is list of names of adjusters which I got by nodupkey on adjuster column in dataset.

PAUL

KATHRYN

EMMA

VICTOR

SAM

 

and I want to create 2 variables 1 storing week number and another storing start date of the week. This is a dummy dataset i am using to merge with the adjuster claim counts I have. So the weeks where adjuster doesn't have any data the count will be zero. 

 

Output should look like:

ADJUSTER    WEEK   Start Dt

PAUL             1            31DEC2018

PAUL             2            07JAN2018

...

PAUL             53          30DEC2018

KATHRYN     1            31DEC2018

KATHRYN     2            07JAN2018

...

KATHRYN     53          30DEC2018 


 

Solution
a month ago
Occasional Contributor
Posts: 8

Re: Do loop to create multiple variables in single data step

Have I got this logic right?

 

You currently have just a list of names. You want to expand that so that for each name you have one observation for each week of the year, with a variable for the week number, and a variable for the start date of that week where weeks start on a Sunday.

 

If that is correct then the below should get you what you want. It will output 52 or 53 observations per name, depending on how close Sunday is to the start of the year.

 

data have;
informat adjuster $20.;
input adjuster;
datalines;
PAUL
KATHRYN
EMMA
VICTOR
SAM
;run;

data want;
set have;
format StartDt date9.;
drop EndDt; /* This is just used to check if we've entered enough weeks to cover the year */
Week= 1;
EndDt= intnx('year',date(),0,'E'); /* End of current year */
StartDt= intnx('year',EndDt,-1,'E'); /* End of last year */
StartDt= intnx('week',StartDt,1,'B'); /* First Sunday of current year */
do while (StartDt <= EndDt);
	output; /* We started with values for week 1 so output a record immediately */
	Week= Week+1; /* Increment the week counter by 1 */
	StartDt= StartDt+7; /* A week is always 7 days long so increment date by 7 */
	/* Another loop will start as long as StartDt is still in the current year */
end;
run;

 

The code assumes week 1 starts on the first Sunday of the year, with any days before that belonging to the last week of the previous year.

PROC Star
Posts: 1,561

Re: Do loop to create multiple variables in single data step

Posted in reply to vpgodbole

nice quiz:

more fun Smiley Happy :

data have;
input id;
cards;
101
102
103
;

data want;
set have;
do n1=51 to 53;
n2=mod(n1,10);
output;
end;
run;

 

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 182 views
  • 2 likes
  • 4 in conversation