Creating new variables from manifestations of a string variable

Accepted Solution Solved
Reply
New Contributor D1
New Contributor
Posts: 2
Accepted Solution

Creating new variables from manifestations of a string variable

Hello everyone,

I’m new to SAS and have been trying to figure out how to solve the following problem:

Let’s assume I have the following dataset:

 

data specialty;

  input  ID  specialty $12. ;

  cards;

1 Neurologist

1 Psychologist

2 GP

3 Neurologist

4 Cardiologist

5 Cardiologist

;

run;

 

What I want to do now is to create a new column for every physician specialty from the column “specialty” and put a “1” in the respective column if it fits and zero otherwise.

So the result should look like this:

ID

specialty

Neurologist

Psychologist

GP

Cardiologist

1

Neurologist

1

0

0

0

1

Psychologist

0

1

0

0

2

GP

0

0

1

0

3

Neurologist

1

0

0

0

4

Cardiologist

0

0

0

1

5

Cardiologist

0

0

0

1

 

I solved the problem with creating every variable manually and lots of “if then statements” for now but I’m not very happy with this. Is there a more elaborated way to achieve the desired outcome?

Thank you!


Accepted Solutions
Solution
‎01-18-2016 10:24 AM
Super User
Posts: 7,447

Re: Creating new variables from manifestations of a string variable

data specialty;
input  ID  specialty $12. ;
x = 1;
cards;
1 Neurologist
1 Psychologist
2 GP
3 Neurologist
4 Cardiologist
5 Cardiologist
;
run;
proc transpose data=specialty out=wide (drop=_NAME_);
by ID;
id specialty;
var x;
run;

This basically provides what you wanted (having missing values instead of zero), but you only get 1 observation for ID=1, where both columns Neuroloigist and Psychologist have the value 1.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎01-18-2016 10:24 AM
Super User
Posts: 7,447

Re: Creating new variables from manifestations of a string variable

data specialty;
input  ID  specialty $12. ;
x = 1;
cards;
1 Neurologist
1 Psychologist
2 GP
3 Neurologist
4 Cardiologist
5 Cardiologist
;
run;
proc transpose data=specialty out=wide (drop=_NAME_);
by ID;
id specialty;
var x;
run;

This basically provides what you wanted (having missing values instead of zero), but you only get 1 observation for ID=1, where both columns Neuroloigist and Psychologist have the value 1.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor D1
New Contributor
Posts: 2

Re: Creating new variables from manifestations of a string variable

Thank you Kurt and RW9 for the quick responses. That is exactly what I needed!

Super User
Posts: 5,388

Re: Creating new variables from manifestations of a string variable

Looks like a report to me. What is the intended use for the output data set?

I believe you can use cross tabulations in PROC FREQ for this.

Data never sleeps
Super User
Super User
Posts: 7,720

Re: Creating new variables from manifestations of a string variable

Well, this will give you what you want:

data specialty;
  input  ID  specialty $12. ;
  res=1;
  cards;
1 Neurologist
1 Psychologist
2 GP
3 Neurologist
4 Cardiologist
5 Cardiologist
;
run;

proc transpose data=specialty out=want;
  by id specialty;
  var res;
  id specialty;
  idlabel specialty;
run;

However I would advise against this approach, if you variables change then your going to need to reprogram.  Its generally a good idea to have a general prefix and an incrementor so you can use arrays, you can still label them what ever your like, but its so much easier to code with:

data specialty;
  input  ID  specialty $12. ;
  res=1;
  cards;
1 Neurologist
1 Psychologist
2 GP
3 Neurologist
4 Cardiologist
5 Cardiologist
;
run;

data want;
  set specialty;
  array var{4} 8.;
  select(specialty);
    when ("Neurologist") var{1}=1;
    when ("Psychologist") var{2}=1;
    when ("GP") var{3}=1;
    when ("Cardiologist") var{4}=1;
    otherwise;
  end;
run;

You can then refer to the array of vars in further processing.

Super User
Posts: 9,875

Re: Creating new variables from manifestations of a string variable

 

data specialty;
input  ID  specialty $12. ;
x = 1;
n+1;
cards;
1 Neurologist
1 Psychologist
2 GP
3 Neurologist
4 Cardiologist
5 Cardiologist
;
run;
proc transpose data=specialty out=wide (drop=_NAME_ n);
by n;
id specialty;
var x;
copy id specialty;
run;
proc stdize data=wide out=want missing=0 reponly;run;
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 372 views
  • 1 like
  • 5 in conversation