wrap subject ids in quotes in macro variable list

[tarheel13]

is there another way to do this: 

proc sql noprint;
   select distinct("'"||usubjid||"'") 
      into: pksubj separated by ',' 
      from pk;
quit;

or is this already the best way? 

[Tom · Accepted solution]

• Use the QUOTE() function as it will handle the case when the value contains the quote character.  You can use the optional second argument to use single quotes instead of double quotes.
• Use the TRIM() function so that the trailing spaces are not included in the quotes, then you can fit more values into the 64K byte limit of a single macro variable.  Might not be so important for SUBJID, unless you have defined it with a ridiculous length.  Do not use STRIP() as leading spaces are significant in comparing values, unlike the trailing spaces.
• The DISTINCT keyword is NOT a function, no need to add those extra parentheses.
• What are you using the macro variable for?  In most cases it will be better to use space as the delimiter.  The IN operator doesn't care which you use but if you tried to pass the value into a macro function the commas could make that hard.

proc sql noprint;
   select distinct quote(trim(usubjid),"'") 
      into :pksubj separated by ' ' 
      from pk
  ;
quit;

.

[tarheel13]

data pk;
   set pk dose;    
   if usubjid in (&pksubj); 
run;

I make PK datasets. there is a lot of code that is copied and pasted but typically the next data step is like this. I mean isn't there any better way to do this? Like couldn't subqueries be used in proc sql or something? it's an entire program where the info from dose records has to be carried forward to pk records. 

I guess the comma could be unnecessary. I do not normally use quote function but I guess it is more efficient/prettier than using the pipes and concatenating the quotes.

[Tom]

---

@tarheel13 wrote:

data pk;
   set pk dose;    
   if usubjid in (&pksubj); 
run;

I make PK datasets. there is a lot of code that is copied and pasted but typically the next data step is like this. I mean isn't there any better way to do this? Like couldn't subqueries be used in proc sql or something? it's an entire program where the info from dose records has to be carried forward to pk records. 

I guess the comma could be unnecessary. I do not normally use quote function but I guess it is more efficient/prettier than using the pipes and concatenating the quotes.

---

That data step looks strange.  Are you really trying to just append two dataset instead of doing a merge?

If you are doing a merge then use the IN= dataset option.

data pk_dose;
  merge pk(in=in1) dose;    
  by usubjid;
  if in1;
run;

If you really did want to just stack the data instead of merging it then here is a way you could still take advantage of the IN= dataset option by interleaving the two sources and remembering if the subject was in the PK dataset.

data pk_dose;
  set pk(in=in1) dose;    
  by usubjid;
  if first.usubjid then in_pk = in1;
  retain in_pk;
  if in_pk;
run;

[tarheel13]

no they cannot be merged. pk and dose need to be combined vertically, not horizontally. is using the in flag also an option with combining datasets vertically? 

[Tom]

---

@tarheel13 wrote:

no they cannot be merged. pk and dose need to be combined vertically, not horizontally. is using the in flag also an option with combining datasets vertically? 

---

Not when simply stacking them.  But it you interleave them you can.  See the update to the previous answer.

[tarheel13]

yea basically want to stack dose and pk but don't want to keep pk subjects that weren't in dose. 

[Tom]

---

@tarheel13 wrote:

yea basically want to stack dose and pk but don't want to keep pk subjects that weren't in dose. 

---

So change the order in the SET statement so the DOSE records for a subject appear before the PK records and test if they subject has a DOSE record.  You probably also want to use WHERE= to exclude any records in the DOSE dataset that indicate that the subject did not actually get dosed.

[tarheel13]

awesome, thanks for responding. I learn so much on here. I'm always looking for the best possible way to do things. 

[SASKiwi]

The QUOTE function is a bit tidier:

proc sql;
  select quote(name)
  into :names separated by ',' 
  from sashelp.class;
quit;

%put &names;

[tarheel13]

I tried that with some fake data and got a trailing blank so maybe you do have to use trim() as @Tom said

[pink_poodle]

Here is another way:
https://communities.sas.com/t5/SAS-Programming/Quote-and-Unquote-a-list/m-p/205514#M38206
But you would need to use proc sql to make a list anyways, so your way could be more convenient in this case. The macro from this post is better when you provide a random typed list that is not a variable.