BookmarkSubscribeRSS Feed
LSchafer
Calcite | Level 5

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,

15 REPLIES 15
Astounding
PROC Star

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
Calcite | Level 5

Thank you so much - I will try this tomorrow.

PGStats
Opal | Level 21

Or else:

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

PG

PG
LSchafer
Calcite | Level 5

Thanks, I will try this tomorrow.

data_null__
Jade | Level 19

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

FriedEgg
SAS Employee

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
Calcite | Level 5

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
SAS Employee

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

LSchafer
Calcite | Level 5

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+
art297
Opal | Level 21

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
Onyx | Level 15

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.Smiley Wink

PGStats
Opal | Level 21

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
FriedEgg
SAS Employee

Thanks for reminding me, I had forgotten that.

LSchafer
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 1732 views
  • 6 likes
  • 7 in conversation