topic Re: data type in SAS Programming

data type

Geo- — Mon, 05 Mar 2018 08:10:38 GMT

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!

Re: data type

Astounding — Mon, 05 Mar 2018 08:03:16 GMT

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.

Re: data type

Geo- — Mon, 05 Mar 2018 08:13:00 GMT

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.

Re: data type

Kurt_Bremser — Mon, 05 Mar 2018 08:30:25 GMT

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)";

Re: data type

RW9 — Mon, 05 Mar 2018 09:28:35 GMT

"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!!!

Re: data type

Kurt_Bremser — Mon, 05 Mar 2018 09:37:17 GMT

@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.

Re: data type

Astounding — Mon, 05 Mar 2018 13:20:36 GMT

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);