DATA Step, Macro, Functions and more

How do I create a new variable , based on the value of existing variable?

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

How do I create a new variable , based on the value of existing variable?

[ Edited ]

Hi all,

 

I have following data, where the state name is embedded in the variable name. I need to create a new variable called state from this.

 

testdata:

periodvar1.IDvar1.ORvar1.NVvar1.WAvar1.CAvar2.IDvar2.ORvar2.NVvar2.WAvar2.CA
1995Q110203040501020304050
1995Q210203040501020304050
1995Q310203040501020304050
1995Q410203040501020304050
1996Q110203040501020304050
1996Q210203040501020304050
1996Q310203040501020304050
1996Q410203040501020304050

 

expected data:

periodstatevar1var2
1995Q1ID1010
1995Q1OR2020
1995Q1NV3030
1995Q1WA4040
1995Q1CA5050
1995Q2ID1010
1995Q2OR2020
1995Q2NV3030
1995Q2WA4040
1995Q2CA5050
1995Q3ID1010
1995Q3OR2020
1995Q3NV3030
1995Q3WA4040
1995Q3CA5050
1995Q4ID1010
1995Q4OR2020
1995Q4NV3030
1995Q4WA4040
1995Q4CA5050

 

Please help me with this.

 

Thanks


Accepted Solutions
Solution
‎12-05-2017 10:28 AM
Super User
Posts: 9,890

Re: How do I c\create a new variable , based on the value of existing variable?

Posted in reply to UshaLatha

This is done with proc transpose:

data have;
infile cards dlm='09'x;
input period $	var1_ID	var1_OR	var1_NV	var1_WA	var1_CA	var2_ID	var2_OR	var2_NV	var2_WA	var2_CA;
cards;
1995Q1	10	20	30	40	50	10	20	30	40	50
1995Q2	10	20	30	40	50	10	20	30	40	50
1995Q3	10	20	30	40	50	10	20	30	40	50
1995Q4	10	20	30	40	50	10	20	30	40	50
1996Q1	10	20	30	40	50	10	20	30	40	50
1996Q2	10	20	30	40	50	10	20	30	40	50
1996Q3	10	20	30	40	50	10	20	30	40	50
1996Q4	10	20	30	40	50	10	20	30	40	50
;
run;

proc transpose data=have out=trans1;
by period;
var var:;
run;

data trans2;
set trans1;
length state $2;
state = scan(_name_,2,'_');
_name_ = scan(_name_,1,'_');
run;

proc sort data=trans2;
by period state;
run;

proc transpose data=trans2 out=want (drop=_name_);
by period state;
var col1;
id _name_;
run;

Note that the only assumption is the prefix and the underline as separator in the original variable name.

Note 2: dots are not valid in SAS variable names, so I changed to underlines.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
PROC Star
Posts: 1,215

Re: How do I c\create a new variable , based on the value of existing variable?

Posted in reply to UshaLatha

How is this data stored? Do you have periods in your variable names?

Contributor
Posts: 28

Re: How do I c\create a new variable , based on the value of existing variable?

yes,

we have a variable named "period" which contains all the quarters in character format

Super User
Posts: 9,890

Re: How do I c\create a new variable , based on the value of existing variable?

Posted in reply to UshaLatha

@draycut noted that dots are not valid in SAS names, so you can't have var1.OR as a variable.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎12-05-2017 10:28 AM
Super User
Posts: 9,890

Re: How do I c\create a new variable , based on the value of existing variable?

Posted in reply to UshaLatha

This is done with proc transpose:

data have;
infile cards dlm='09'x;
input period $	var1_ID	var1_OR	var1_NV	var1_WA	var1_CA	var2_ID	var2_OR	var2_NV	var2_WA	var2_CA;
cards;
1995Q1	10	20	30	40	50	10	20	30	40	50
1995Q2	10	20	30	40	50	10	20	30	40	50
1995Q3	10	20	30	40	50	10	20	30	40	50
1995Q4	10	20	30	40	50	10	20	30	40	50
1996Q1	10	20	30	40	50	10	20	30	40	50
1996Q2	10	20	30	40	50	10	20	30	40	50
1996Q3	10	20	30	40	50	10	20	30	40	50
1996Q4	10	20	30	40	50	10	20	30	40	50
;
run;

proc transpose data=have out=trans1;
by period;
var var:;
run;

data trans2;
set trans1;
length state $2;
state = scan(_name_,2,'_');
_name_ = scan(_name_,1,'_');
run;

proc sort data=trans2;
by period state;
run;

proc transpose data=trans2 out=want (drop=_name_);
by period state;
var col1;
id _name_;
run;

Note that the only assumption is the prefix and the underline as separator in the original variable name.

Note 2: dots are not valid in SAS variable names, so I changed to underlines.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 28

Re: How do I c\create a new variable , based on the value of existing variable?

Posted in reply to KurtBremser

hi,..

one question. what is var: in this code mean?? do I need to specify all variable names?

 

proc transpose data=have out=trans1;
by period;
var var:;
run;

Super User
Posts: 9,890

Re: How do I c\create a new variable , based on the value of existing variable?

Posted in reply to UshaLatha

The colon is a wildcard character, similar to the asterisk in WIndows or UNIX. So

var:

simply means "all variables where the name starts with 'var'".

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 28

Re: How do I c\create a new variable , based on the value of existing variable?

Posted in reply to KurtBremser

Thanks !!!! The code works perfectly !!!

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 139 views
  • 3 likes
  • 3 in conversation