Manthan, I believe this article shows what you want: http://blogs.sas.com/content/sasdummy/2014/01/29/using-filename-zip/ ... View more

To speedup your operation, be sure there are indexes on the num column in both tables. Also a question: You said the num column is character where the leading may be "0". Is '0000123' the same as '123' for your purposes? If they are then you run into a problem where your character field may introduced unwanted results due to inability to match because of leading zero's in the num column. If that is the case convert the num in both tables to a number field. (see next part of comment) We do a lot of our SAS from large tables natively in MS-SQL or Oracle (100's of GB or larger per table) and I tend to create temporary tables with the appropriate "keep" values in the set to limit the data returned to me as only the minimal dataset that I need. I can then make any changes or indexes I need to the local data, and then if I need to upload any back I can do so in a single stream without any local functions to take time. This moves the processing completely into the realm of my control and since it is all local, I don't have to worry about network time either. This scheme works faster, than any other I have tried when I have to process any amount of the data locally. ... View more

You can make a table with only the list of reps you want, this will not have anything other than the reps, then you can do outer joins to the tables you are using with the conditions. This will give you two result tables both containing the reps you need and null data, except rep id's, for missing data. Since both of the tables have been prequalified, then you can join them and get the result you seek. (yes, ugly and messy way of it.) ... View more

We often do that with the month of our observation period. We have our first month of observation start at 1 and then by month year we increment though our observation period. This gives a convenient way to track many things including the fiscal year. Here is a format we use: proc format;   value FYfmt       1- 12 = 1998      13- 24 = 1999      25- 36 = 2000      37- 48 = 2001      49- 60 = 2002      61- 72 = 2003      73- 84 = 2004      85- 96 = 2005      97-108 = 2006     109-120 = 2007     121-132 = 2008     133-144 = 2009     145-156 = 2010     157-168 = 2011     169-180 = 2012     181-192 = 2013     193-204 = 2014   205-216 = 2015   217-228 = 2016   229-240 = 2017   241-252 = 2018   253-264 = 2019   265-276 = 2020     ; run; Please note, our observation period, month 1, is our first month of our fiscal year and does not follow the calendar year. ... View more

proc sql;   create table test (myID num,                      myOne varchar(5),                      myTwo num,       myThree varchar(5),       myFour num,       myFive num,       mySix varchar(5));   insert into test values(1, '', null, '3', 4, 5, '6');   insert into test values(2, '1', null, '3', 4, 5, '');   insert into test values(3, '', 2, '3', null, 5, '');   insert into test values(4, '1', 2, '3', null, 5, '6');   insert into test values(5, '1', 2, '3', 4, 5, ''); quit; /* This will give you the user id along with a string containing the name of each colum that was missing or null. colum list is in a funky order because of using two seperate arrays. */ data myOut (replace=yes compress=no);   set test;   array myChar _CHARACTER_;   array myNum _NUMERIC_;   myFlag = 0;   length myMissing $ 300;   length myMissingOne $ 300;   do _i=1 to dim(myChar);     if(myChar[_i] = '') then do;         myMissingOne = VNAME(myChar[_i]);   myMissing = catx(' ', myMissing, myMissingOne);   myFlag=1;   end;   end;   do _i=1 to dim(myNum);     if(myNum[_i] = .) then do;    myMissingOne = VNAME(myNum[_i]);    myMissing = catx(' ', myMissing, myMissingOne);    myFlag = 1; end;   end;   if(myFlag = 1) then output;   keep myID myMissing;   run; /* This will give you a dataset listing all of the columns that contain either null or missing. The columns are tied to the client ID through a one to many relationship in this table. One client ID and many columns. The client ID/Column relation is unique. */ data myOutTwo (replace=yes compress=no);   set test;   array myChar _CHARACTER_;   array myNum _NUMERIC_;   myFlag = 0;   length myMissing $ 300;   do _i=1 to dim(myChar);     if(myChar[_i] = '') then do;         myMissing = VNAME(myChar[_i]);   output;   end;   end;   do _i=1 to dim(myNum);     if(myNum[_i] = .) then do;    myMissing = VNAME(myNum[_i]);    output; end;   end;   keep myID myMissing;   run; ... View more

Here is how I would go about solving the problem: make an input array for your input dataset, make 2 variables for your output dataset--ID and Missing, use VTYPE to check the data type of each variable in your input array. use appropriate number or character functions to do your checking concatinate the character representation for your index for your input array onto an output string each time you find a missing or null value. set a flag saying you have missing values output your string if you have any missing. keep client number and string showing missing loop until done. do what you'll get is: an ID field and a string filed, the string field will contain all missing indexes. [1] [2 4] [2] [1 3 4 6] [3] [2 3 5] [4] [2 4 5] [5] [2 3 4 5] ... View more

Jesse, Thank you for further defining your requirements, you need to move back to a dataset ordered by MemberID and Admit_Date. Use a manual "output" that is triggered by your MemberID or your Admit_Date changing. Each iteration you have to "retain" your calculation values and only keep the calculated values for your output. Are you familiar with using "output" with a dataset? Les ... View more

Jse, You are correct, it does not group by Member ID and Date, but then neither does your example output data. That is why I add the comment: If you want to have your answer by MemberID and Admit_Date, then just add Admit_Date to the "group by" clause: "group by MemberID, Admit_Date". This will give:    select MemberID, Admit_Date, sum(cost) as Episode_Cost, 1 as Episode_Count, Count(Distinct(Admit_Date)) as Episode_Span from MyStuff group by MemberID, Admit_date; And if you want to be assured sequential MemberID's and Admit_Date's you need an "order by" clause" which will give: select MemberID, Admit_Date, sum(cost) as Episode_Cost, 1 as Episode_Count, Count(Distinct(Admit_Date)) as Episode_Span from MyStuff group by MemberID, Admit_date order by MemberID, Admit_date; Which I believe is what you asked for, but did not show, in the first place. I am still at a loss as to what you want to do with your group field: in your sample output it is taken from the first obs of the group by MemberID--in both cases shown, it has been ignored. ... View more

For needs like you describe I tend to use Proc SQL. The following will create a table, populate it, and give you the described result using Proc SQL. proc sql; create table myStuff(MemberID Char(4),                                  Admit_Date Date,                                  cost int,                                  Group char(16));   insert into myStuff values('1111', '01-JAN-2010'd, 3, 'Unavoidable');   insert into myStuff values('1111', '01-JAN-2010'd, 2, 'Avoidable');   insert into myStuff values('1111', '02-JAN-2010'd, 4, 'Unavoidable');   insert into myStuff values('2222', '20-JAN-2010'd, 1, 'Avoidable');   select MemberID, Min(Admit_Date) as Admit_Date, sum(cost) as Episode_Cost, 1 as Episode_Count, Count(Distinct(Admit_Date)) as Episode_Span from MyStuff group by MemberID; quit; If you want to have your answer by MemberID and Admit_Date, then just add Admit_Date to the "group by" clause: "group by MemberID, Admit_Date". ... View more