The request from @ChrisNZ was not to see your code, the request was to provide the sample data in a usable form for us, that usable form is a SAS data step and can be created by the instructions at the link he gave.
... View more
@Sotarkadin wrote:
Thanks for the replys all. I am working on understanding the array. It worked the first run through, I have one question/alteration. There can be instances where all the dates are empty bc no action has been taking yet. When this happened it returned the status as open since thats the first column it reads. Is there a way for it to leave status blank if none of them have any dates?
The solution I posted leaves status blank when all of Open through close are missing.
... View more
Yes, the value from TABLE2 would overwrite that from TABLE1. If you set the following SAS system option, SAS will inform you in the log about which variables are overwritten and in which "direction":
options msglevel=I;
Here's an example of the type of log messages you will get with this option setting:
INFO: The variable cm_num on data set WORK.TABLE1 will be overwritten by data set WORK.TABLE2.
Edit: The above message occurred when I used ref_nbr as the only BY variable. BY variables will not occur in these messages, because they have to be in both datasets and by nature of match merging it's impossible that one of them is overwritten by a different value.
... View more
Good day, @Sotarkadin,
Try this:
data have;
input acnt_nbr dispute_nbr $ act_seq act_name $ trns_nbr;
cards;
12345 abcd 1 XY 9876
12345 abcd 1 YZ .
12345 abcd 1 CV .
12345 abcd 2 XY 5678
12345 abcd 2 YZ .
12345 abcd 2 CV .
;
data want;
do until(last.act_seq);
set have;
by acnt_nbr dispute_nbr act_seq notsorted;
if ~missing(trns_nbr) then tn=trns_nbr;
end;
do until(last.act_seq);
set have;
by acnt_nbr dispute_nbr act_seq notsorted;
if missing(trns_nbr) then trns_nbr=tn;
output;
end;
drop tn;
run;
Edit: This assumes that your input dataset is at least grouped by the three BY variables, i.e., groups of identical combinations of their values form contiguous blocks of observations.
... View more
%let n=%sysevalf(%sysfunc(rand(uniform))*10+30,i);
int(rand('uniform')*10) will generate the random number 0,1,2,3,4,5,6,7,8,9
here 10 is the range between 30 and 40 .
here 30 is the base of range(or left value of range) .
... View more
CALL SYMPUTX() is new (less than 10 years old ). It makes the process of generating macro variables from data step easier in that it both trims the leading and trailing spaces and quietly converts numbers to text. Note that macro variables are always text strings since they are just a tool for building text. The use of DEQUOTE() function allows you to easily generate the variable with single quotes around it as your original code was trying to do. Macro variables do not resolve inside of quoted strings that start with a single quote. If you are ok with making the macro variable use double quotes then you do not need that extra complexity. %let filepath="/filepath/merchant_&mnth._&yr..csv" ; Or your can leave off the quotes and add them back later when you reference the variable. %let filepath=/filepath/merchant_&mnth._&yr..csv ;
... View more
By using Libname we can bring all the spreadsheets in excel workbook to SAS environment in the form of datasets. The spreadsheets names will be the Dataset names followed by $. For example if Sample.xls is the workbook that consists of Acc, AccNums, CustIds as spreadsheets then if you use libname statement then Acc$, AccNums$, CustIds$ will be the dataset names. Libname Libref Excel "Path\Sample.xls"; Libref is a user defined name.
... View more
If you have another question start another thread. Include some sample data that is representative of the join you want to do. Show have and want.
... View more
