topic Select into question in SAS Programming

Select into question

LSchafer — Wed, 28 Mar 2012 21:04:12 GMT

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,

Select into question

Astounding — Wed, 28 Mar 2012 21:15:26 GMT

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.

Select into question

PGStats — Wed, 28 Mar 2012 21:18:18 GMT

Or else:

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

Select into question

data_null__ — Wed, 28 Mar 2012 21:36:06 GMT

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

Select into question

FriedEgg — Wed, 28 Mar 2012 22:13:46 GMT

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;

Select into question

LSchafer — Wed, 28 Mar 2012 23:30:15 GMT

Thanks, I will try this tomorrow.

Select into question

LSchafer — Wed, 28 Mar 2012 23:30:41 GMT

Thank you so much - I will try this tomorrow.

Re: Select into question

LSchafer — Wed, 28 Mar 2012 23:35:28 GMT

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.

Select into question

LSchafer — Wed, 28 Mar 2012 23:38:22 GMT

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

Select into question

FriedEgg — Wed, 28 Mar 2012 23:58:11 GMT

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

Select into question

PGStats — Thu, 29 Mar 2012 01:14:00 GMT

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.

Select into question

FriedEgg — Thu, 29 Mar 2012 01:39:27 GMT

Thanks for reminding me, I had forgotten that.

Select into question

Haikuo — Thu, 29 Mar 2012 02:22:24 GMT

I am aware that better approachers have been offered, just in case if you ever wonder how to play the macro variable in place:

%let var=1 2 3 4 5;

%let varlist=;

%macro test;

%local i;

%do i=1 %to %sysfunc(countw(&var));

%let varlist=&varlist.%str( )v%scan(&var,&i);

%end;

%mend;

%test

%put &varlist;

Haikuo

Re: Select into question

LSchafer — Thu, 29 Mar 2012 12:57:11 GMT

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+

Re: Select into question

art297 — Thu, 29 Mar 2012 13:23:48 GMT

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;

Re: Select into question

Haikuo — Thu, 29 Mar 2012 13:32:33 GMT

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.