I don't know if you do can do it in one if statement but you can use the open function to check if the dataset exists. see open function, page 2 of the following paper. http://www2.sas.com/proceedings/sugi26/p095-26.pdf ... View more

The first left join, joins the tables together based on the full id and weight from this is stored in variable weight1. The second left join joins the tables together based on the FSA and the weight from this is stored in the variable weight2 The case statement is almost the equivalent of a if then else or a select statement. See here: http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473682.htm so right now it checks for a missing value in weight1, if so then assigns the weight2 value. You can add in a line to the case statement that checks for missing both and then set it to 1. If you can't figure it out, post your code and I'll help to debug it. HTH ... View more

Yes this is a problem, output statements are defined at compile time I believe, not execution. Once you have an explicit output statement there is no more implicit output and you need to have an output statement where you want an output. As you noted the else statement won't execute so there won't be any output!. ... View more

There's a typo in your postal code list... but this is what you can do using proc sql [pre] data have1; input id postal $; cards; 1 K0A3K0 2 K0A8PN 3 K0A3N0 4 K0A8T6 ; run; data have2; input postal $ weight; cards; K0A 1 K0A3K0 0.69 K0A3N0 0.74 ; run; proc sql; create table want as select h1.id, h1.postal, h2.weight as weight1, h2_s.weight as weight2, case when h2.weight=. then h2_s.weight else h2.weight end as weight from have1 as h1 left join have2 as h2 on h1.postal=h2.postal left join have2 as h2_s on substr(compress(h1.postal), 1,3)=compress(h2_s.postal) order by id; quit; [/pre] ... View more

Easiest method is to create a new variable, FSA and merge on that. Another way is to use proc SQL and an update statement (untested). proc sql; update have h set weight=(select weight from fsaldu as f where substr(h.fsaldu, 1,3)=f.fsaldu and h.weight=.); quit; ... View more

If your ID's are always the same length, ie 9 as in your description the following works. If not then you'll need a different solution, using retain and length statement,where you basically append the new read value onto the string after encoding it. data want; groupid='AD245CD32'; length code: $2. j: $1.; array code(9) $ code1-code9; array temp(9) $ j1-j9; do i=1 to 9; temp=substr(groupid, i, 1); code=put(temp, $xwalk.); end; new_code=cats(of code1-code9); run; ... View more

Look at the put function... You need to build in the loop and the code to concatenate the strings together rather than output like I did, but hopefully you get the idea. You can create a proc format from a dataset with cntlin option if you need to. HTH, Reeza ie. data want; groupid='AD245'; length new_j $2. ; do i=1 to 5; j=substr(groupid, i,1); new_j=compress(put(j, $xwalk.)); output; end; run; ... View more

Depends on how you want to call it and what your macro does... 1. Change your macro so that the first parameter is handled as a variable list 2. Create another macro that loops through your list of ids in a dataset and calls the macro. If you may want to do a few ID's at a time rather than a whole dataset you may want to consider 1 as the most viable option. ... View more

BallardW answer is perfectly correct. Another data struture that might be useful is var1 var2 --- vara varb 1 0 ---1 0 Basically a column for each possible answer and a 1 or 0 if that occurs. You can do this from ballardw dataset by adding an observation 1 and transposing the data using proc transpose. ... View more

According to the logic you provided it should be a 1. ie same account number and same id and the dates are less than 2 days apart. Is there some other logic? ... View more

Ideally I think this should be done via Hash tables if the data can fit in memory. The method that comes to mind for me is a sql join on itself. I'm pretty sure the code won't work below but its the idea. This questions has been asked around health care data on this site so some googling might help find a closer solution. ie proc sql; create table test as select a.*, (select count(*) from have b where a.account=b.account and a.id=b.id and a.date-b.date<2) as hit from have a; quit; ... View more

Questions first: 1. Do you have to have a macro or will a datastep or Proc SQL work? 2. How are you defining hit exactly? maybe I'm missing something but it doesn't look like 48 hours between ids in the sample data set. ... View more

If the dataset is large and creating a second copy of the dataset would be cumbersome then this is a more efficient way. However if you have duplicate results in your join you can run into issues. [pre] data have1; set sashelp.class; *need to set space for it; format empty_var $25.; empty_var=''; run; proc sql; update have1 as a set empty_var=(select b.name from sashelp.class as b where a.name=b.name); quit; [/pre] ... View more

I tried and created an access db with only 6 records and it works fine with either proc import or the libname statement, also SAS 9.2 TS2M3. I do notice that your extension is accdb though and not sure what the difference is, mine is mdb. Two possibilities, check how SAS is displaying this variable, it may have read it in right, but have the wrong format set. Or if the first time this happens is later in the dataset and you might need to play with the guessing rows options similar to excel. HTH, Reeza ... View more

Probably using Proc SQL proc sql; create table a as select a.*, b.varb from a left join b on a.vara=b.vara; quit; Problem is having multiples, then you need to figure out how to deal with those, depending on your data logic. If you create an empty var in an earlier step you can update instead of creating a new table. ... View more