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:
variable | start | end | woe | beta |
age | -999999999 | 57853 | -1 | 3 |
age | 57853 | 89756 | -1.1 | 3 |
age | 89756 | 897452 | -1.2 | 3 |
age | 897452 | 9999999999 | -1.3 | 3 |
agnc_non_ccbt_fnd_bal | -999999999 | 4235 | 2 | 3 |
agnc_non_ccbt_fnd_bal | 4235 | 65785 | 3 | 3 |
agnc_non_ccbt_fnd_bal | 65785 | 9999999999 | 4 | 3 |
marriage | -999999999 | 9673 | 3.1 | 3 |
marriage | 9673 | 75341 | 2.1 | 3 |
marriage | 75341 | 98543 | 1.1 | 3 |
marriage | 98543 | 567864 | 0.1 | 3 |
marriage | 567864 | 9999999999 | -1 | 3 |
education | -999999999 | 8376 | 5 | 3 |
education | 8376 | 93847 | 6 | 3 |
education | 93847 | 9999999999 | 7 | 3 |
loan | -999999999 | 56753 | 3 | 2 |
loan | 56753 | 5465624 | 4 | 2 |
loan | 5465624 | 9999999999 | 1 | 2 |
please check the question color in red,thanks!
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);
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.
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.
"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!!!
@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.
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);
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)";
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.