Does the following still give an error ? libname xf Excel path="F:\share\check.xls"; ... View more

Hi, Just as information, and to keep your datastep example, you migt use the following code (which makes the same as the PROC SQL provided by DBailey). As long as the observations exist in the dataset1, they will appear in the merged dataset (with empty values in the fields normally coming from the dataset2). data merged;       merge dataset1 (in = inDS1)                 dataset2 (in = inDS2);       by MutualfundID year month;       if inDS1; run; ... View more

Hi, Here is an example with three different date formats adding a slash as separator in between the year, month and day values (this is achived by the 's' after the date format. It may also be a blank, colon, dash, period or no separator): data want;   date='25JAN2013'd;   date1 = put(date, ddmmyys10.);   date2 = put(date, mmddyys10.);   date2 = put(date, yymmdds10.); run; Regards, Florent ... View more

I think he wanted to know how to create the 'time' variable from the 'value' variable instead of reading both. Your example is just reading values, there is no calculation. Regards, Florent ... View more

Hi Sravan, Is this what you want to achieve ? data want;      set have;      format time tod8.;      time = value * 86400; run; Kr, Florent ... View more

The following code works: %MACRO Test(ST);      %IF "&ST" = "OR" %THEN %DO;            %PUT State is Oregon.;      %END;      %ELSE %DO;            %PUT State is not Oregon.;      %END; %MEND; %Test(OR); ... View more

Hi, Concerning the first point, it is clear that the "ID" is not available in table A. It should be replaced by "patient_id". For the second bullet point, I do not agree with the fact that the line concerning hospital 103 and patient 3 should be excluded from the table. If you look at tables A and B, you will find this patient-hospital combination in both of them. Thus this is logical to find it back in your final table when using inner joins. I hope it helps. ... View more

When using the "inner join"/"left join"/"right join" syntax, you cannot put a comma after the join condition(s). Furthermore, you don't have to repeat the table names which have already been used in a join. With your code, it gives: Proc sql;     create table want as     select a.id, a.los, b.hospital_id, b.wtcases, c.postcode, d.area     from a     inner join b            on a.patient_id = b.patient_id          and a.hospital_id = b.hospital_id     left join c            on b.hospital_id = c.hospital_id     left join d            on c.postcode=d.postcode; quit; proc print data=a noobs; run; proc print data=b noobs; run; proc print data=c noobs; run; proc print data=d noobs; run; proc print data=want; run; ... View more

Hi, I do not understand why you have a "proc sql" in your code, it is useless. What does the following code gives ? data qc2; input cm13 key_str  key_expire  feed_key  first_use  last_use; cards; 3710100000000              102158168           0  2.0083E8   19940118     10101 3710100000000               22608716           1  2.0083E8   20010601  19940117 ; run; data tty1;   set qc2;   if last_use=10101 then enddt_new=99991231;   else enddt_new=last_use; run; proc sort data=tty1; by  cm13  descending enddt_new descending first_use  ; run; ... View more

The first step (table d) contains everything which is in A and B, and potentially in C. When there is no match with the table C then the content of the category field will be empty. If you want to have table e created at the same time as table d (so in one step), then you should use make a dastep using the merge statement and play with the inA, inB, inC variable you can create at that time. The disadvantage of making a datastep instead of a proc sql is that it requires you firstly sort tables a, b and c in the same order... so in the end you would have 3 sort statements + 1 datastep. There is no way to create two datasets at a time with a "create table" statement of the proc sql. I hope it answers to your questions. ... View more

Here you go: data test;       *set AAA;    var = 1;       call symput('var', var);    var2 = symget('var'); run; ... View more

Hi, Would the following piece of code do what you are expecting ? proc sql; create table d as     select  a.id     ,          b.breakfast     ,          c.category     from a     inner join b          on a.id = b.id     left join c          on b.breakfast = c.breakfast; quit; data e;      set d (where=(missing(category))); run; Kind regards, Florent ... View more