06-04-2015 06:52 PM
I have the following code below; the idea is to run the 'type' macro in order to generate a different .xml file for every value of FICO in the &book._file dataset. Below also please find a sample of the &book._file dataset. I am stuck at the point below indicated by the arrows. I need to assign the length of the macro varaible array created in the proc sql step to the newlycreated macro-variable FICO_dim.
SAMPLE DATASET &book._file
LoanID FICO OLTV UPB
37294 650 75 242,454
54298 600 70 435,982
54217 580 65 532,983
55321 632 65 525,245
11123 580 65 567,346
84372 710 65 334,653
98324 674 70 562,252
/*****MACRO BEGINS HERE************/
create table &book._file as
case when nbr_dcsn_fico_all_borr<620 then 619
when nbr_dcsn_fico_all_borr>=620 and nbr_dcsn_fico_all_borr<660 then 659
when nbr_dcsn_fico_all_borr>=660 and nbr_dcsn_fico_all_borr<700 then 699
when nbr_dcsn_fico_all_borr>=700 and nbr_dcsn_fico_all_borr<740 then 739
when nbr_dcsn_fico_all_borr>=740 and nbr_dcsn_fico_all_borr<780 then 779
when nbr_dcsn_fico_all_borr>=780 and nbr_dcsn_fico_all_borr<850 then 780
else 999 end as FICO,
case when pct_ptfl_ltv_orign<=60 then 60
when pct_ptfl_ltv_orign>60 and pct_ptfl_ltv_orign<=70 then 70
when pct_ptfl_ltv_orign>70 and pct_ptfl_ltv_orign<=75 then 75
when pct_ptfl_ltv_orign>75 and pct_ptfl_ltv_orign<=80 then 80
when pct_ptfl_ltv_orign>80 and pct_ptfl_ltv_orign<=90 then 90
when pct_ptfl_ltv_orign>90 then 100 else 199 end as OLTV
/*case when cd_mrtg_purp_pp='RC' then 'Refi-CO'
when cd_mrtg_purp_pp in ('RN','RZ') then 'Refi-Non CO' else 'Purchase' end as Purpose*/
order by id_loan_syst_gend;
select distinct FICO
into :fico1 - :fico9999
%let FICO_dim=dim(&FICO); -----> I am stuck here; is there a way to assign the value of the length of the macro array created in the proc sql step above, to the newly-created
-----> macro-variable FICO_dim?
%do i=1 %to &FICO_dim;
set &book._file end=last;O
06-04-2015 07:36 PM
For & FICO_dim, you could get from:
select distinct FICO ,count(distinct FICO )
into :fico1 - :fico9999,:FICO_dim
06-04-2015 08:35 PM
The answer to your question about how many distinct values of FICO did your SQL step find is the automatic variable SQLOBS.
%let fico_dim = &sqlobs ;
It looks like your macro is trying to overwrite the same XML file multiple times. You probably need to include the value of FICO in the filename so that each file is distinct. Also your data step is not writing anything to the file that it is creating.
Also note that you can generate a separate file for each value of FICO without the need for a macro.
Use the FILEVAR option on the FILE statement.
set &book._file ;
filevar = cats( "/fmacdata/utility/cmgt/dev/EC/adco/test/&book._file",FICO, ".xml");
file xml filevar=filevar ;
put .... ;