topic Re: Assigning Date Macro Variable to a Date MMDDYY10. in SAS Programming

Assigning Date Macro Variable to a Date MMDDYY10.

Kody_devl — Thu, 28 Jul 2016 13:22:02 GMT

I am Looping thru a dataset and want to assign a date to a macro variable (&FDay / &LDay). The underlying table's data is formated as MMDDYY10.

Here is my Code and the log file restult that show a number value for the data input (sample of log commented in the code.

The actual first and last dates are: 01/06/2016 and 01/14/2016.

Any help is greatly appreciated!

%macro Loop_Loans;

%Do i=1 %To 100 %By 1;

%Let cnt = %Eval(&i.);

data _Null_;

set LOANS_First_Last_Date (obs=&i firstobs=&i);

call symputx('vLoan_Number', Loan_Number);

call symputx('vFDay',Date_Source_FirstDay);

call symputx('vLDay', Date_Source_LastDay);

run;

%Put &cnt. and the LoanNumber is &vLoan_Number. and FDay is &vFDay. and LDay is &vLDay.;

Here are the variable values in the LOG.

100 and the LoanNumber is 2200571630 and FDay is 20453 and LDay is 20527

%End;

%Mend Loop_Loans; %Loop_Loans;

Re: Assigning Date Macro Variable to a Date MMDDYY10.

RW9 — Thu, 28 Jul 2016 13:41:16 GMT

I am not sure why? To print out the values in a dataset:

proc print data=LOANS_First_Last_Date;

run;

I am not sure what the point in your code is, a datastep IS a loop, it iterates through each observation in the dataset. Why try to put loads of macro code around it to do that purpose?

Also to note, macro is text generation only, it has no structures for datatypes other than text, so isn't the best tool for data processing.

Perhaps post test data - in the form of a datastep - and what you are trying to do with that data.

Re: Assigning Date Macro Variable to a Date MMDDYY10.

Kody_devl — Thu, 28 Jul 2016 16:11:26 GMT

This code is only a snapshot of the whole picture:

I have a dataset that is a list of days all the way out to the year 2040. I want to calculate the number or workdays that the Loan is in the "system". To do this, I must look up first day that the loan (hence loan number) entered the system AND the last day that it left the system. Once, the vaiables hold the beginning and ending dates, I will calculate the number of work days using my "Days" lookup table like this.

/* Set Variable for Work Days */

proc sql noprint;

SELECT

/* Wrk_Days */

(COUNT(t1.Today)) format n8. into :cntWrkDays

FROM WORK.DAYS t1

WHERE t1.Today >='6Jan2016'd AND t1.Today <= '29Feb2016'd AND t1.WorkDay = 'Yes';

QUIT;

%Put The Workdays Are &cntWrkDays.; /* Gives-->The Workdays Are 39 */

Re: Assigning Date Macro Variable to a Date MMDDYY10.

RW9 — Thu, 28 Jul 2016 14:56:48 GMT

Well, I don't know about either your code or your data, as I can't see either. But if you need to know the number of days between two rows of data - assuming row x is the first time, and row y is the last record, then a simple min/max for each subject will get you what you want. See code below.

proc sql;
  select  CUST_ID,
          min(DATE) as X,
          max(DATE) as Y,
          max(DATE) - min(DATE) as NUM_DAYS
  from    HAVE
  group by CUST_ID;
quit;

Re: Assigning Date Macro Variable to a Date MMDDYY10.

Kody_devl — Thu, 28 Jul 2016 16:13:42 GMT

Yes, I am familiar with MIN and MAX. But the Date range will include weekend days.

I must take the count that you calculated and remove the Weekend days from the count. That is why I want to use a table that marks the workdays. Here is the "Days" dataset and the query in my previous post will correctly count the work days (and correctly exclude the weekend days) using the Starting and ending Date in the data ranges for each loan (134,000 loans to calculate).

See Screenshot of the "Days" dataset (attached .png).

Re: Assigning Date Macro Variable to a Date MMDDYY10.

RW9 — Thu, 28 Jul 2016 16:24:52 GMT

Well, I don;t have any test data so this is just an example:

data have;
  cust_id=1; 
  do date="01jan2015"d to "13apr2015"d;
    output;
  end;
  format date date9.;
run;

data want;
  set have;
  by cust_id;
  retain count;
  if first.cust_id then count=0;
  if weekday(date) not in (1,7) then count=count+1;
  if last.cust_id then output;
run;

Weekday function will tell you 1 or 7 if its weekend, so just exclude them from the count.

Re: Assigning Date Macro Variable to a Date MMDDYY10.

Kody_devl — Thu, 28 Jul 2016 18:28:31 GMT

RW9

Your code is brilliant!

I just still don't see how to pass the Loan_Number, First_Date, and Last Date into it (the code) using the values from my loan table. That is where I am stuck. My End desire is to have a table / List with this format:

Loan_Number Wrk_Days_In_System

1200489472 25

do date="01jan2015"d to "13apr2015"d; <--------------- How do I get these dates into your code (114,000)?

I apologize for not sending some actual records.

I have masked a few records and pasted into excel the two Dataset that I am working with.

Re: Assigning Date Macro Variable to a Date MMDDYY10.

RW9 — Fri, 29 Jul 2016 08:14:32 GMT

Sorry, I don't download excel files. Post test data as a datastep in the text box.

Why would you need to pass anything in? You have data which contains rows for each loan_number and date - that is the data you are processing. On that data you then run your counting logic. There is no looping, no creating any other data, no maco calling, none of that is necessary. You have some data, you run a datastep over the data, that is all.

Re: Assigning Date Macro Variable to a Date MMDDYY10.

Kody_devl — Fri, 29 Jul 2016 14:18:02 GMT

I am very, very close to getting this to run! Your help is greatly appreciated.

Below is your code modified to my data:

The output is perfect except it is not looping to do the counts.

I have attached a screen shot of the output data (which is the same as the input data (plus the count and date)).

Notice: the count returned = 1 (because it is not looping First to Last).

Here is the error message:

ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero,

or invalid.

Looks like i need to convert my date format from mmddyy10. to date9. to get the loop function to work? How would you be doing that?

data Work.LOANS_FIRST_LAST_DATE;

Loan_number=2200571630;

do date=Date_Loan_FirstDay to Date_Loan_LastDay;

output;

end;

format date date9.;

run;

data Work.LOANS_FIRST_LAST_DATE_NEW;

set Work.LOANS_FIRST_LAST_DATE;

by LOAN_NUMBER;

retain count;

if first.LOAN_NUMBER then count=0;

if weekday(date) not in (1,7) then count=count+1;

if last.LOAN_NUMBER then output;

run;

Re: Assigning Date Macro Variable to a Date MMDDYY10.

ballardw — Fri, 29 Jul 2016 14:42:57 GMT

@Kody_devl wrote:

I am very, very close to getting this to run! Your help is greatly appreciated.

Below is your code modified to my data:

The output is perfect except it is not looping to do the counts.

I have attached a screen shot of the output data (which is the same as the input data (plus the count and date)).

Notice: the count returned = 1 (because it is not looping First to Last).

Here is the error message:

ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero,

or invalid.

Looks like i need to convert my date format from mmddyy10. to date9. to get the loop function to work? How would you be doing that?

data Work.LOANS_FIRST_LAST_DATE;

/*

Loan_number=2200571630;

*/

do date=Date_Loan_FirstDay to Date_Loan_LastDay;

output;

end;

format date date9.;

run;

data Work.LOANS_FIRST_LAST_DATE_NEW;

set Work.LOANS_FIRST_LAST_DATE;

by LOAN_NUMBER;

retain count;

if first.LOAN_NUMBER then count=0;

if weekday(date) not in (1,7) then count=count+1;

if last.LOAN_NUMBER then output;

run;

I see two issues. First the the data set Work.LOANS_FIRST_LAST_DATE does not have any place where you assign values to Date_Loan_FirstDay or Date_Loan_LastDay. So you requested a Do . to .; in efffect causing the error message.

Also in your initial post you were using the variables Date_Source_FirstDay and Date_Source_LastDay. Just wondering if the result is going to be what you want there.

Re: Assigning Date Macro Variable to a Date MMDDYY10.

Kody_devl — Fri, 29 Jul 2016 15:14:34 GMT

Hi Ballard,

I am a developer in everything else, but SAS.
To be honest, I am not sure how you would assign a the value of the underlying table to a variable.
That is what I was trying to doing using the macro in the original post.
The responses were that macros variables could not receive and assign a date format. (Here were my results from that experiment). So I gave up one that method (even though was correctly loop thru the source table).

Here are the variable values in the LOG.

Count No 100 and the LoanNumber is 2200571630 and FDay is 20453 and LDay is 20527 (Note: The source table stores 01/31/2016 (mmddyy10. as the date format so why do I get 20453?)

Up higher in the posted this Macro code:
%macro Loop_Loans;
%Do i=1 %To 100 %By 1;
%Let cnt = %Eval(&i.);

data _Null_;

set LOANS_First_Last_Date (obs=&i firstobs=&i);
call symputx('vLoan_Number', Loan_Number);
call symputx('vFDay',Date_Source_FirstDay);
call symputx('vLDay', Date_Source_LastDay);
run;

%Put &cnt. and the LoanNumber is &vLoan_Number. and FDay is &vFDay. and LDay is &vLDay.;

Here are the variable values in the LOG.

100 and the LoanNumber is 2200571630 and FDay is 20453 and LDay is 20527

%End;

%Mend Loop_Loans; %Loop_Loans;

Your thoughts?

Re: Assigning Date Macro Variable to a Date MMDDYY10.

RW9 — Sat, 30 Jul 2016 10:51:07 GMT

Yes, I would suggest you look at how SAS works first. It isn't like other languages, and it sounds like you are trying to force code through macro to emulate what you would do in other languages. Macro language is nothing more than a text generator, it is not really a programming language. Base SAS is the programming language, it works by processing dataset, observation by observation in a loop. So task one is to put your data into a dataset, then work out the logic to process the data row by row.

For your problem, start by defining your input data, where do these first and last dates come from? Post some examples of this original data - as a datastep so e have something to work with - my original code was based on the assumption you had a dataset, so that is where we need to start.

Re: Assigning Date Macro Variable to a Date MMDDYY10.

Kody_devl — Mon, 01 Aug 2016 13:10:30 GMT

RW9

You are exactly correct. I am trying to emulate a looping process as I would use in other languages. My approach, looping thru the dataset and being able to pass data value to variables in any data type / format is logical an intuitive for me.

I did not understand what you just explained and now, I am starting to get it.

What I am trying to do is relatively simple.

I could send some data in excel but am under the impression that you don't like excel files, even it is .xlsx (vs xlsm). If Excel .xlsx OK (no macros contained) I could attach 100 records of sample data. I don't know how to write code to accually generate the data.

Here is a some simple data example of what I am trying to do.

Step 1. Daily_Loan_file.txt (no files are gernerated on weekend). ---------> imported to a sas table / dataset.

Step 2. Here are the key fields in the SAS table (Comment: I can change the date format to date9. or other formats if helpful).

Table #1

Loan_Number *(n8.) Source_Text_Tile (Char40) Source_Date (Char10) Source_Date_Dte (MMDDYY10.)

125 Loan_Detail 01012016.txt 010116 01/01/16

125 Loan_Detail 01042016.txt 010416 01/04/16

125 Loan_Detail 01052016.txt 010516 01/05/16

125 Loan_Detail 01062016.txt 010616 01/06/16

125 Loan_Detail 01072016.txt 010716 01/07/16

125 Loan_Detail 01082016.txt 010816 01/08/16

Step 3. Summarize the first dataset into another that looks liks this (I used Min / MAX to do this easily).

Table #2

LN_NBR First_Day Last_Day No_Days count_Files No_Work_Days

125 01/01/2016 01/08/2016 8 6 6 <----------- This is the number that I am trying to calculate because, if the file count doesn't equal the no_Work_Days, I know that I am either:

1. Missing a file (txt) or

2. The loan dropped from the system for a day or 2 or 10 days, and then is re-instates. I want to identify the broken chains. If the file count was 4 and the work days is 6, I know something is wrong.

The first table (before summarized) has 5,000,000 records (Jan16-Jul16).

Table #2 has 117,000 loans so I have 117,000 workdays to calculate.

The code that you gave me does the calculation perfectly.

How do I go from hard codes dates to passing 114,0000 values thru the calculation.

I appreciate your hanging in there with me! I working with a couple of 15 year SAS developers and they couldn't instinctively tell me how to do this or fix my code.

PS - If you have code that you can run against a dataset to generate the data I could trry to get the code that produces the data.

"Post some examples of this original data - as a datastep so e have something to work with - my original code was based on the assumption you had a dataset, so that is where we need to start."

Screen Shot of Table 2 attached with date9. format

LET me know what else I can send.

Re: Assigning Date Macro Variable to a Date MMDDYY10.

Cynthia_sas — Mon, 01 Aug 2016 14:36:59 GMT

Hi:

I recommend taking a step back and looking at the basics of how a DO loop works with and without macro values. To answer this question, specifically:

do date="01jan2015"d to "13apr2015"d; <--------------- How do I get these dates into your code (114,000)

Consider this example:

And then compare that to using just the numeric value for 7/15 or 20650 and then 20655 as the end date for July 20:

and, finally, consider this example, using sashelp.prdsale, which has some date values...you have 2 possible choices for having SQL build your macro variables. And depending on how SQL builds your macro variables will determine how you use the macro variables in any other code. With the first example, the date returned is the number of days since Jan 1, 1960, which can be used as a simple numeric constant in subsequent code, but with the second example, the date returned is a formatted date string that would have to be quoted as "&fstart"d and "&fend"d in subsequent code:

cynthia

Re: Assigning Date Macro Variable to a Date MMDDYY10.

RW9 — Mon, 01 Aug 2016 14:42:33 GMT

Here is some code and test data which shows the process after importing the file. The data loops over each row, retaining a count as it goes. To get days is simply taking one date from another, to get work days, I add in another loop to loop over all the days between the dates, and adding to the totla if its a weekday. I would really advise doing the Base SAS trainning and getting familiar with working with SAS data as it is different to other languages.

data have;
  id=125;
  file="Loan_Detail 01012016.txt";
  source_date="010416";
  source_date_dte="01apr2016"d;
  output;
  source_date="010516";
  source_date_dte="01may2016"d;
  output;
  source_date="010616";
  source_date_dte="01jun2016"d;
  output;
run;

data want (drop=i);
  set have;
  by id;
  retain first_date last_date days count_files count_workdays;
  if first.id then do;
    first_date=source_date_dte;
    count_files=1;
    days=1;
    if weekday(source_date_dte) not in (1,7) then count_workdays=1;
    else count_workdays=0;
  end;
  else do;
    count_files=count_files+1;
    days=days + (source_date_dte - first_date);
    do i=first_date to source_date_dte;
      if weekday(i) not in (1,7) then count_workdays=count_workdays+1;
    end;
  end;
  if last.id then do;
    last_date=source_date_dte;
    output;
  end;
  format first_date last_date date9.;
run;

Re: Assigning Date Macro Variable to a Date MMDDYY10.

Kody_devl — Tue, 02 Aug 2016 19:05:02 GMT

RW9

I got it to work and, going thru the modifications, I see how it works!

This has opened the door to previously unknown data issues for my client, unfairly making me, as a result of your exemplary efforts (and patience), the hero!

I cannot thank you enough!

SAS does the summary calculations of the 5 million records in an impressive 10 Seconds (I do index the tables)!

Can you recommend any Base SAS training on video as I am a

kinesthetic and auditory learning?

Thanks Again;

Kody_devl

Re: Assigning Date Macro Variable to a Date MMDDYY10.

RW9 — Tue, 02 Aug 2016 15:56:10 GMT

Well, when they give you a knighthood...

As for your request, SAS themselves provide videos on the basic stuff - you have to pay for the more expensive ones:

http://support.sas.com/training/tutorial/

(Some of the interface is different as its based on the learning edition of SAS).

Also, this popped up when searching:

https://www.youtube.com/watch?v=XXTH6pDPmb0

Yoi could give that a look as well, see if it helps.

That should give you a good grounding in the basics. Once you want to get more advanced, then you need to split your topics up and focus. Good reference points are:

Graphs: http://blogs.sas.com/content/graphicallyspeaking/

General:

Here, this community has a wealth of information

Also, http://www.lexjansen.com/ holds a weatlh more information.