Creating contextual variables

[Sledgehamma]

Hello all,

I'm very new to SAS and I'm trying to do the following:

based upon whats inside each observation I want to create a new variable.

My data is structured as follows:

ID     var1    var2    var3
1       x:1       y:1     z:5
2       z:3       y:7     x:5
3       x:9       y:4     z:4
4       y:1       z:1     x:5
5       y:2       x:15   z:9

where x may be $sum of investments, y may be interest rates and so on.

The data is very unstructured so that var1 has bascially no meaning as it can include values for x,y and z. So my goal is a strucutred data such that:

ID    x      y     z

1      1     1     5

2      5     7     3

3      9     4     4

4      5     1     1

5    15     2     9

I'm thankfull for every input.

[PeterClemmensen]

You say that your data is unstructured.. But do you have x, y and z in each of your observations? Or do you have observations with missing values or where eg y appears twice?

[Sledgehamma]

Yes, there can be some missing values. I forgot to mention that.

[Ksharp · Accepted solution]

Assume I understood your question.

data have;
input ID     (var1    var2    var3 ) ($);
cards;
1       x:1       y:1     z:5
2       z:3       y:7     x:5
3       x:9       y:4     z:4
4       y:1       z:1     x:5
5       y:2       x:15   z:9
;
run;
data temp;
 set have;
 array x{*} $ var:;
 do i=1 to dim(x);
   name=scan(x{i},1,':');
   value=scan(x{i},-1,':');
   output;
 end;
keep id name value;
run;
proc transpose data=temp out=want(drop=_:);
by id ;
id name;
var value;
run;

[PeterClemmensen]

If your data represents your actual problem, then do something like this

data have;
input ID (var1-var3)($);
infile datalines dlm=',';
datalines;
1,x:1,y:1,z:5
2,z:3,y:7,x:5
3,x:9,y:4,z:4
4,y:1,z:1,x:5
5,y:2,x:15,z:9
;

data temp(drop=i var:);
   set have;
   array _{3} var1-var3;
   call sortc(of _[*]);
   do i=1 to dim(_);
      vname=scan(_[i], 1, ':');
      value=input(scan(_[i], 2, ':'), best8.);
      output;
   end;
run;

proc transpose data=temp out=want(drop=_name_);
   by id;
   id vname;
   var value;
run;

[novinosrin]

Hello @Sledgehamma 

If your letter pattern is X, Y and Z in any unordered pattern, it's way too simple

data have;
input ID     (var1    var2    var3 ) ($);
cards;
1       x:1       y:1     z:5
2       z:3       y:7     x:5
3       x:9       y:4     z:4
4       y:1       z:1     x:5
5       y:2       x:15   z:9
;
run;

data want;
set have;
array t(*) var:;
array j(*)$ X Y Z;
call sortc(of var:);
do i=1 to dim(t);
t(i)=compress(t(i),,'kd');
j(i)=t(i);
end;
drop i var:;
run;

 

[Sledgehamma]

Thank you all for your very fast and helpful solution! Seems to be a great community 🙂

@Ksharp: You understood my Problem perfectly 🙂 I tried the solution you provided and it worked for my case.

Sadly, it showed that my data is even more badly structured than I thought. So for some "X" there is no mention of it. So instead of "X:5" there is only "5". However, since is actually not a 5 but a word, which I can clearly identify as variable x I think/hope I can work something out.