how can i use macro in scan function? how can i define "," as delimiter?

[alparle]

Hi everyone, 

%let alparle = NCEPA7,NCEPA8;   (note: ncepa7 and ncepa8 are names of variables in work.datahave)

proc sql;  create table levo as select ALT,

scan("&alparle." ,1,  ",") 

from work.datahave ; quit;  

i want to call only NCEPA7 as variable to my table but the scan function output is "ncepa7" for this reason,

my tablo look like as :

ALT	_TEMA001
ANC	NCEPA7
NON_MARK	NCEPA7
MARK_SPEC	NCEPA7
ANC	NCEPA7

But i want to get the table as :

ALT	NCEPA7
ANC	5
NON_MARK	7
MARK_SPEC	15
ANC	1

so which function should i use or how can i eliminate the  quotation marks? 

thank you in advance Best Regards.

[russt_sas]

You just need to change this:

scan("&alparle." ,1,  ",") 

to this:

scan(&alparle ,1,  ",") 

This will give you the output you showed.

[Quentin]

I'm confused, if NCEP7 is a variable that exists in datahave, what are its values? It looks like you're trying to overwrite that variable with new values (5, 7, 15, 1)?  What is the meaning of those new values, how do you want to calculate them?

[ballardw]

When you run this code:

%let alparle = NCEPA7,NCEPA8;   (note: ncepa7 and ncepa8 are names of variables in work.datahave)
 
proc sql;  create table levo as select ALT,
scan("&alparle." ,1,  ",") 
from work.datahave ; quit;  
 

The macro variable Alparle is replaced by the defintion.

So SAS sees this as code:

%let alparle = NCEPA7,NCEPA8;   (note: ncepa7 and ncepa8 are names of variables in work.datahave)
 
proc sql;  create table levo as select ALT,
scan("NCEPA7,NCEPA8" ,1,  ",") 
from work.datahave ; quit;  
 

So the SCAN finds the first result in the literal string which is NCEPA7

If  you expect SAS to use code something like this where NCEPA7 is a variable in the Datahave:

proc sql;  create table levo as select ALT,
NCEPA7 
from work.datahave ; quit;  

And especially if you expect to go on to creating some sort of loop to get multiple variables you should describe the whole problem.

[alparle]

thanks, it works with " |"  as delimiter but i could not find the define comma as delimiter. 

[Tom]

---

@alparle wrote:

thanks, it works with " |"  as delimiter but i could not find the define comma as delimiter. 

---

So if you use " |" as the delimiter in a %SCAN() call that means that it should treat any of the three characters double quote, space or vertical bar as a possible delimiter.

If you want to use comma as the delimiter then you need to use macro quoting (or actual quoting) to make sure the commas do not get seen by SAS as argument delimiters instead of as part of the string.

Macro quoting:

%let alparle = NCEPA7,NCEPA8;  
proc sql;  
create table levo as
  select ALT, %scan(%quote(&alparle),1,%str(,)) 
  from work.datahave 
; 
quit;  

Actual quote characters.

%let alparle = NCEPA7,NCEPA8;  
proc sql;  
create table levo as
  select ALT, %scan("&alparle",1,",") 
  from work.datahave 
; 
quit;  

The only added value of throwing space in as an additional delimiter characters is eliminates any spaces that might exist around the actual delimiters.

Example:

48   %let list= A  ,  B;
49   %put |%scan("&list",1,",")|;
|A  |
50   %put |%scan("&list",1," ,")|;
|A|

[Tom]

If you want to generate code then use MACRO LOGIC and not SAS LOGIC.

So use the macro function %SCAN() instead of the SAS function SCAN().

Note it will be much easier if you use any other character then comma.  For example you could use |.

%let alparle = NCEPA7|NCEPA8;  
proc sql;
create table levo as 
  select ALT, %scan(&alparle.,1,|) 
  from work.datahave 
; 
quit;  

And if you want to use your variable list in normal SAS code then use space as the delimiter.

%let alparle = NCEPA7 NCEPA8;  
data levo_alparle;
  set datahave (keep=alt &alparle);
run;

To tell %SCAN() that space is the delimiter use macro quoting.

%let alparle = NCEPA7 NCEPA8;  
proc sql;
create table levo as 
  select ALT, %scan(&alparle.,1,%str( )) 
  from work.datahave 
; 
quit;