DATA Step, Macro, Functions and more

Select into question

Reply
Occasional Contributor
Posts: 17

Select into question

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,

Super User
Posts: 5,515

Select into question

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.

Occasional Contributor
Posts: 17

Select into question

Posted in reply to Astounding

Thank you so much - I will try this tomorrow.

Respected Advisor
Posts: 4,927

Select into question

Or else:

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

PG

PG
Occasional Contributor
Posts: 17

Select into question

Thanks, I will try this tomorrow.

Respected Advisor
Posts: 3,799

Select into question

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

Trusted Advisor
Posts: 1,301

Select into question

Posted in reply to data_null__

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;

Occasional Contributor
Posts: 17

Re: Select into question

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.

Trusted Advisor
Posts: 1,301

Select into question

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

Occasional Contributor
Posts: 17

Re: Select into question

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):

  

TESTVALUEVISIT
A16   1.00
A20   2.00
A25   3.00
B8   1.00
BNEGATIVE   1.00
B7   2.00
BNEGATIVE   2.00
B6   3.00
BNEGATIVE   3.00
C61.00
C5   2.00
C4.5   3.00
Test20   1.00
Test24.6   3.00
Test51+   3.00
GLUC6   1.00
GLUCNEGATIVE   1.00
GLUC4   2.00
GLUCNEGATIVE   2.00
GLUC3.2   3.00
GLUCNEGATIVE   3.00

My desired result

TESTVISIT
123
A162025
B876
BNegNegNeg
C654.5
test20Missing4.6
GLUC643.2
GLUCNegNegNeg
test5MissingMissing1+
PROC Star
Posts: 7,486

Re: Select into question

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 _Smiley Happy;

  by test subtest;

  var value;

  id visit;

run;

Respected Advisor
Posts: 3,156

Re: Select into question

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=_Smiley Happy let prefix=visit;

by test _flag;

var value;

run;

proc print;run;

Update: Oops, Art beats me on this one.Smiley Wink

Respected Advisor
Posts: 4,927

Select into question

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

PG
Trusted Advisor
Posts: 1,301

Select into question

Thanks for reminding me, I had forgotten that.

Occasional Contributor
Posts: 17

Select into question

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

Ask a Question
Discussion stats
  • 15 replies
  • 680 views
  • 6 likes
  • 7 in conversation