DATA Step, Macro, Functions and more

How to Expand a dataset using value from one of the column

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

How to Expand a dataset using value from one of the column

Hello everyone,

I have an aggregated dataset that I want to expand for further calculation. Does anyone know I could achieve this in data step programming.

 

Dataset have

 

 

data have; 
input name $ group $ mo_freq $ value ; 
datalines; 
wlf_01 actv 1-4 0
wlf_01 actv 5-7 1.15
wlf_01 actv 8-10 1.00
wlf_02 dflt 1-4 0
wlf_02 dflt 5-6 5.15
wlf_02 dflt 7-8 4.25
wlf_02 dflt 9-10 0
wlf_03 prog 1-10 2.23
;
run;

 

 

dataset want

 

name

group

mo_freq

month

value

wlf_01

actv

1-4

1

0.00

wlf_01

actv

1-4

2

0.00

wlf_01

actv

1-4

3

0.00

wlf_01

actv

1-4

4

0.00

wlf_01

actv

5-7

5

1.15

wlf_01

actv

5-7

6

1.15

wlf_01

actv

5-7

7

1.15

wlf_01

actv

8-10

8

1.00

wlf_01

actv

8-10

9

1.00

wlf_01

actv

8-10

10

1.00

wlf_02

dflt

1-4

1

0.00

wlf_02

dflt

1-4

2

0.00

wlf_02

dflt

1-4

3

0.00

wlf_02

dflt

1-4

4

0.00

wlf_02

dflt

5-6

5

5.15

wlf_02

dflt

5-6

6

5.15

wlf_02

dflt

7-8

7

4.25

wlf_02

dflt

7-8

8

4.25

wlf_02

dflt

9-10

9

0.00

wlf_02

dflt

9-10

10

0.00

wlf_03

prog

1-10

1

2.23

wlf_03

prog

1-10

2

2.23

wlf_03

prog

1-10

3

2.23

wlf_03

prog

1-10

4

2.23

wlf_03

prog

1-10

5

2.23

wlf_03

prog

1-10

6

2.23

wlf_03

prog

1-10

7

2.23

wlf_03

prog

1-10

8

2.23

wlf_03

prog

1-10

9

2.23

wlf_03

prog

1-10

10

2.23

 

 


Accepted Solutions
Solution
‎05-21-2018 01:09 PM
Super User
Posts: 13,523

Re: How to Expand a dataset using value from one of the column

Key is getting the numeric values from the character variable mo_freq;

data want; 
input name $ group $ mo_freq $ value ; 
do month = input(scan(mo_freq,1),best.) to ( input(scan(mo_freq,2),best.));
   output;
end;
datalines; 
wlf_01 actv 1-4 0
wlf_01 actv 5-7 1.15
wlf_01 actv 8-10 1.00
wlf_02 dflt 1-4 0
wlf_02 dflt 5-6 5.15
wlf_02 dflt 7-8 4.25
wlf_02 dflt 9-10 0
wlf_03 prog 1-10 2.23
;
run;

View solution in original post


All Replies
Solution
‎05-21-2018 01:09 PM
Super User
Posts: 13,523

Re: How to Expand a dataset using value from one of the column

Key is getting the numeric values from the character variable mo_freq;

data want; 
input name $ group $ mo_freq $ value ; 
do month = input(scan(mo_freq,1),best.) to ( input(scan(mo_freq,2),best.));
   output;
end;
datalines; 
wlf_01 actv 1-4 0
wlf_01 actv 5-7 1.15
wlf_01 actv 8-10 1.00
wlf_02 dflt 1-4 0
wlf_02 dflt 5-6 5.15
wlf_02 dflt 7-8 4.25
wlf_02 dflt 9-10 0
wlf_03 prog 1-10 2.23
;
run;
☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 82 views
  • 1 like
  • 2 in conversation