topic Re: Creating contextual variables in New SAS User

Creating contextual variables

Sledgehamma — Tue, 14 May 2019 13:30:01 GMT

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.

Re: Creating contextual variables

PeterClemmensen — Tue, 14 May 2019 13:35:53 GMT

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?

Re: Creating contextual variables

Ksharp — Tue, 14 May 2019 13:47:09 GMT

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;

Re: Creating contextual variables

PeterClemmensen — Tue, 14 May 2019 13:51:20 GMT

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;

Re: Creating contextual variables

novinosrin — Tue, 14 May 2019 14:09:23 GMT

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;

Re: Creating contextual variables

Sledgehamma — Wed, 15 May 2019 06:19:14 GMT

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

Re: Creating contextual variables

Sledgehamma — Wed, 15 May 2019 06:51:21 GMT

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.