Assigning macro variable value to regular variable

Frequent Contributor
Posts: 121

Assigning macro variable value to regular variable

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

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************/

%macro type(book);

proc sql;
  create table &book._file as
    select *,
    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*/
    from &book.
    order by id_loan_syst_gend;

proc sql;
  select distinct FICO
  into :fico1 - :fico9999
  from &book._file;

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

data _null_;
file "/fmacdata/utility/cmgt/dev/EC/adco/test/&book._file.xml";
set &book._file end=last;O
where FICO=fico&i;




Super Contributor
Posts: 275

Re: Assigning macro variable value to regular variable

For & FICO_dim, you could get from:

proc sql;

  select distinct FICO ,count(distinct  FICO )

  into :fico1 - :fico9999,:FICO_dim

  from &book._file;


Super User
Super User
Posts: 6,842

Re: Assigning macro variable value to regular variable

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.

data _null_;

  set &book._file ;

  filevar = cats( "/fmacdata/utility/cmgt/dev/EC/adco/test/&book._file",FICO, ".xml");

  file xml filevar=filevar ;

  put .... ;


Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation