Select into question

[LSchafer]

Hi,

I have a specific problem -

I am reading variable names into a macro variable. This will later be used as the column names for a transpose function which is implemented using a data step. Note - I have already tried the TRANSPOSE function, but it will not work as there are "multiple values within the by group."

Howwver, visitnum is numeric. How can I use a format or put statement to change the values as follows -

select distinct visitnum into :varlist separated by '  ' from outlib.AAA_1;  results in --> varlist = "1 2 3 4 5 ..."

I want varlist = "V1 V2 V3 V4 V5...";

Thanks,

[Astounding]

You will undoubtedly see a few choices offered.  Here's a neat way:

select distinct visitnum into : varlist separated by ' V' from outlib.AAA_1;

%let varlist = V&varlist;

Good luck.

[LSchafer]

Thank you so much - I will try this tomorrow.

[PGStats]

Or else:

select distinct cats("V", visitnum) into :varlist separated by ' ' from outlib.AAA_1;

PG

[LSchafer]

Thanks, I will try this tomorrow.

[data_null__]

Show your data.  You might be able to use PROC TRANSPOSE, and if nothing else you have your QC program written too.

[FriedEgg]

I agree with data _null_;  it is likely that PROC TRANSPOSE will be able to do what you are looking for and your data step program is not necessary unless you are transposing many hundreds of variables or you are performing additional summarization on data as you transpose or a number of other caveats.  If you want the best response present your full case.

As a note to PG's method use cats('V',put(visitnum,best.)) or similar format in order to avoid the numeric->character conversion note, if you want.  Also you could build a picture format

proc format;

picture vnbr other = '00009' (prefix='V');

run;

select distinct visitnum format=vnbr. into :varlist separated by ' ' from outlib.AAA_1;

[LSchafer]

Hi, as I mentioned - PROC TRANSPOSE doesn't work as there are "multiple values within the by group." I already have implemented that solution, but the data is dirty, so it won't work.

Thanks for the proc format tip - I was heading in that direction, but just don't know enough about proc format.

[FriedEgg]

I mean no offense but just because you attempted implementing a solution the didn't work doesn't mean it cannot be done.

[LSchafer]

No offense taken. Although I have programmed in many other languages, I am new to SAS.

This is a typical clinial trial lab value program, but the number and names of tests varies per subject, as does the number of visits, so the data is not predicable. Tests B and GLUC have multiple values for the same visit - not sure why, but the program has to work with it.

Sample Data (all for the same subject):

TEST	VALUE	VISIT
A	16	1.00
A	20	2.00
A	25	3.00
B	8	1.00
B	NEGATIVE	1.00
B	7	2.00
B	NEGATIVE	2.00
B	6	3.00
B	NEGATIVE	3.00
C	6	1.00
C	5	2.00
C	4.5	3.00
Test2	0	1.00
Test2	4.6	3.00
Test5	1+	3.00
GLUC	6	1.00
GLUC	NEGATIVE	1.00
GLUC	4	2.00
GLUC	NEGATIVE	2.00
GLUC	3.2	3.00
GLUC	NEGATIVE	3.00

My desired result

TEST	VISIT
	1	2	3
A	16	20	25
B	8	7	6
B	Neg	Neg	Neg
C	6	5	4.5
test2	0	Missing	4.6
GLUC	6	4	3.2
GLUC	Neg	Neg	Neg
test5	Missing	Missing	1+

[art297]

I think there is at least one error in your desired result table.  If so, would something like the following suffice?:

data have;

  input TEST $ VALUE $ VISIT;

  cards;

A          16             1.00

A          20             2.00

A          25             3.00

B          8             1.00

B          NEGATIVE             1.00

B          7             2.00

B          NEGATIVE             2.00

B          6             3.00

B          NEGATIVE             3.00

C          6          1.00

C          5             2.00

C          4.5             3.00

Test2          0             1.00

Test2          4.6             3.00

Test5          1+             3.00

GLUC          6             1.00

GLUC          NEGATIVE             1.00

GLUC          4             2.00

GLUC          NEGATIVE             2.00

GLUC          3.2             3.00

GLUC          NEGATIVE             3.00

;

data have;

  set have;

  if value eq "NEGATIVE" then do;

    value="Neg";

    subtest=2;

  end;

  else subtest=1;

run;

proc sort data=have;

  by test subtest;

run;

proc transpose data=have prefix=visit

               out=want (drop=subtest _:);

  by test subtest;

  var value;

  id visit;

run;

[Haikuo]

Not exactly what you want, but, pretty close:

data have;

input (TEST     VALUE) (:$)     VISIT;

_flag=ifn(value='NEGATIVE',0,1);

cards;

A     16        1.00

A     20        2.00

A     25        3.00

B     8        1.00

B     NEGATIVE        1.00

B     7        2.00

B     NEGATIVE        2.00

B     6        3.00

B     NEGATIVE        3.00

C     6     1.00

C     5        2.00

C     4.5        3.00

Test2     0        1.00

Test2     4.6        3.00

Test5     1+        3.00

GLUC     6        1.00

GLUC     NEGATIVE        1.00

GLUC     4        2.00

GLUC     NEGATIVE        2.00

GLUC     3.2        3.00

GLUC     NEGATIVE        3.00

;

proc sort data=have;

by test _flag;

run;

proc transpose data=have out=want(drop=_:) let prefix=visit;

by test _flag;

var value;

run;

proc print;run;

Update: Oops, Art beats me on this one.

[PGStats]

Fried, the cats, catx, catt, and catq functions are made explicitly to accept arguments of both types. Numeric ones are fornatted and trimmed silently. Neat.

PG

[FriedEgg]

Thanks for reminding me, I had forgotten that.

[LSchafer]

Thank you all for your quick assistance. I really appreciate it. I have definitely learned a few new tricks about formatting!!