DATA Step, Macro, Functions and more

data type

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

data type

[ Edited ]

proc sql;
select distinct variable into:numVarList separated by ' ' from Map_num;
quit;

I got a macro array:

variable
age
agnc_non_ccbt_fnd_bal
marriage
education
loan

 

%put &numVarList.;

result:

age agnc_non_ccbt_fnd_balmarraige education loan

 

%put %sysfunc(scan(&numVarList.,1,str()));

result:

age agnc_non_ccb

what's wrong with this?does it supposed to be 'age'?

 

%put %sysfunc(scan(&numVarList.,1));

result:

age

 

 

and I am going to join  two table:

proc sql;

create table numVar_test as

select start into:start_num separated by ' ' from Map_num

where variable=%sysfunc(scan(&numVarList.,1));

result:

could not find column age.why would be this?

 

proc sql;

create table numVar_test as

select start into:start_num separated by ' ' from Map_num

where variable=%sysfunc(scan(&numVarList.,1,str()));

result is could not be match by the where clause

 

 

proc sql;

create table numVar_test as

select start into:start_num separated by ' ' from Map_num

where variable='age';

the result is what I am looking for.

 

 

table Map_num:

variablestartendwoebeta
age-99999999957853-13
age5785389756-1.13
age89756897452-1.23
age8974529999999999-1.33
agnc_non_ccbt_fnd_bal-999999999423523
agnc_non_ccbt_fnd_bal42356578533
agnc_non_ccbt_fnd_bal65785999999999943
marriage-99999999996733.13
marriage9673753412.13
marriage75341985431.13
marriage985435678640.13
marriage5678649999999999-13
education-999999999837653
education83769384763
education93847999999999973
loan-9999999995675332
loan56753546562442
loan5465624999999999912

 

please check the question color in red,thanks!


Accepted Solutions
Solution
‎03-07-2018 06:58 PM
Super User
Posts: 6,542

Re: data type

Here are a few suggestions.  However, I agree with the thought that you are doing the wrong thing by trying to learn SAS language and macro language at the same time.  You should focus on SAS language first and forget about macro language for at least 6 months.

 

For the %PUT statement:

 

%put %scan(&numvarlist, 1, %str( ) );

 

For the WHERE clause:

 

where variable=scan("&numvarlist", 1);

View solution in original post


All Replies
Super User
Posts: 6,542

Re: data type

The source of the problem is the delimiter.  This would be the correct delimiter:

 

%str( )

 

When using this delimiter:

 

str()

 

This is actually saying that there are 5 characters that can all serve as a delimiter:  "s"  "t"  "r"  "("  and  ")"

 

Also note that there is a %SCAN function.  You don't need to apply %SYSFUNC to SCAN.

Contributor
Posts: 36

Re: data type

Posted in reply to Astounding

thank you.So how exactly the where clause should be?please write out the code,I am new in SAS.And plus,is %SYSFUNC should be used when there is Macro.

Super User
Super User
Posts: 9,211

Re: data type

"please write out the code,I am new in SAS." - then why on earth are you writing code like this in the first place?  Thats like trying to lear by jumping in a formula 1 car blinfolded.  Lear Base SAS - which is the programming language - thoroughly.  Once you know that 100% you will realise that you do not need macro ever.  Then you can look at effective areas for incorperating some macro code to make sharing code, or generic tools easier.  Macro does not replace Base SAS!!!

Super User
Posts: 9,593

Re: data type


Geo- wrote:

thank you.So how exactly the where clause should be?please write out the code,I am new in SAS.And plus,is %SYSFUNC should be used when there is Macro.


"I am new in SAS"

 

Learn to walk before you try to run. Meaning, you should work through the basic tutorials available with SAS UE first before engaging in complicated programming like you are trying with this code. If you don't want to do that, hire a contractor/consultant.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎03-07-2018 06:58 PM
Super User
Posts: 6,542

Re: data type

Here are a few suggestions.  However, I agree with the thought that you are doing the wrong thing by trying to learn SAS language and macro language at the same time.  You should focus on SAS language first and forget about macro language for at least 6 months.

 

For the %PUT statement:

 

%put %scan(&numvarlist, 1, %str( ) );

 

For the WHERE clause:

 

where variable=scan("&numvarlist", 1);

Super User
Posts: 9,593

Re: data type

[ Edited ]
proc sql;
create table numVar_test as
select start into:start_num separated by ' ' from Map_num
where variable=%sysfunc(scan(&numVarList.,1));

If the result of the scan into &numvarlist is a string, you need to use quotes, otherwise SAS will mis-interpret it as a variable name. Do this instead:

proc sql;
create table numVar_test as
select start into:start_num separated by ' ' from Map_num
where variable="%scan(&numVarList.,1)";

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 146 views
  • 1 like
  • 4 in conversation