topic Re: Basic SQL question, I think. Dynamiclally assigning variables. in SAS Programming

Basic SQL question, I think. Dynamiclally assigning variables.

n6 — Thu, 30 Jul 2020 18:49:36 GMT

Here is what I have:

var_name var_value

x 10

x 15

x 20

y 55

y 60

y 65

Here is what I want:

x y

10 55

15 60

20 65

It's a little more complicated than that but if I get help doing this I think I can figure the rest out by itself. The answer may have something to do with "call symput" but I'm not sure. I find SQL very hard to figure out on my own.

Re: Basic SQL question, I think. Dynamiclally assigning variables.

n6 — Thu, 30 Jul 2020 18:52:22 GMT

Oops, I forgot the ID var, which might confuse you a little. Here it is.

ID var_name var_value
1    x    10
2    x    15
3    x    20
1    y    55
2    y    60
3    y    65

Here is what I want:

ID x    y
1 10    55
2 15    60

3 20 65

Re: Basic SQL question, I think. Dynamiclally assigning variables.

Tom — Thu, 30 Jul 2020 18:55:57 GMT

SQL is not the tool for this.

But you also do not have enough information in what you posted to make the output you requested. Why did you pair X=15 with Y=55? Why not X=15 and Y=65? You need a third variable.

data have;
  input row name :$32. value ;
cards;
1 x                      10
2 x                      15
3 x                      20
1 y                      55
2 y                       60
3 y                      65
;
proc sort data=have ;
  by row name ;
run;
proc transpose data=have out=want;
  by row;
  id name;
  var value;
run;

Re: Basic SQL question, I think. Dynamiclally assigning variables.

PaigeMiller — Thu, 30 Jul 2020 18:56:32 GMT

proc sql;
    create table want as select a.id,a.var_value as x,b.var_value as y
    from have(where=(var_name='x')) as a left join 
        have(where=(var_name='y')) as b
        on a.id=b.id
    order by a.id;
quit;

Re: Basic SQL question, I think. Dynamiclally assigning variables.

n6 — Thu, 30 Jul 2020 19:09:10 GMT

I see what you mean. I tried to keep it simple and I ended up overdoing it.

I started with proc transpose but decided I'd need sql instead. Okay, let me fool around with proc transpose and see if I can do it.

Re: Basic SQL question, I think. Dynamiclally assigning variables.

Reeza — Thu, 30 Jul 2020 19:43:26 GMT

In a traditional DB SQL you would use PIVOT or OVER type clause. SAS doesn't support that in PROC SQL, PROC TRANSPOSE is a much more dynamic solution overall.

Re: Basic SQL question, I think. Dynamiclally assigning variables.

n6 — Thu, 30 Jul 2020 19:49:50 GMT

Okay, Take 2.

Here's what I have

ID FormSet var_name var_value;

1    50 x 1
1    50 y 2
1    50 z 3
1    55 x 4
1    55 y 5
1    55 z 6
2    60 x 7
2    60 y 8
2    60 z 9
2    65 x 10
2    65 y 11
2    65 z 12

Here is what I want

ID FormSet    x y z
1 50    1    2 3
1 55    4 5    6
2 60    7 8    9

I'm following around with various proc transpose combinations but it hasn't worked out yet. Any help is appreciated.
2 65 10 11 12

Re: Basic SQL question, I think. Dynamiclally assigning variables.

n6 — Thu, 30 Jul 2020 19:55:41 GMT

Oops, that last line of numbers is supposed to go before the text, as you probably guessed.

Re: Basic SQL question, I think. Dynamiclally assigning variables.

n6 — Thu, 30 Jul 2020 19:58:13 GMT

One thing that is vexing me is that if I do "by ID FormSet" in proc transpose it doesn't do a by on all combinations of ID and FormSet but rather concatenates ID and FormSet into one variable. There's probably a reason for that, but still.

Re: Basic SQL question, I think. Dynamiclally assigning variables.

n6 — Thu, 30 Jul 2020 20:04:15 GMT

Holy cow, it worked! Here is the code I used, which I was hinted to (or maybe I was told exactly) by the person whose name I can't see right now, but thanks whoever you are.

proc transpose data=test out=test_out;
   by StudyID FormSet;
   id Var_Name;
   var var_value;
run;

proc print data=test_out; run;

Re: Basic SQL question, I think. Dynamiclally assigning variables.

Tom — Thu, 30 Jul 2020 21:09:49 GMT

@n6 wrote:

One thing that is vexing me is that if I do "by ID FormSet" in proc transpose it doesn't do a by on all combinations of ID and FormSet but rather concatenates ID and FormSet into one variable. There's probably a reason for that, but still.

You probably did ID statement instead of BY statement. BY statement is pretty universal in SAS and always does the same thing. ID statement is specific to PROC TRANSPOSE (although some other procs also use it for slightly different purpose).