Basic SQL question, I think. Dynamiclally assigning variables.

[n6]

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.

[n6]

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

[PaigeMiller]

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;

[n6]

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;

[Tom]

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;

[n6]

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.

[Reeza]

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.

[n6]

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

[n6]

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

[n6]

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.

[Tom]

---

@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).