You might want to check the dataset options firstobs= and obs= Richard ... View more

Wolverine I had to reformat your SQL before I could answer your question.  I assume that the concatenation occurred when you pasted from source code - I've never had that problem. Anyway: ? The problem is that SAS seems to ignore the subquery -- SRVC_BGNCount doesn't even exist in the resulting data file. @ Well, if you want the count in the output dataset you need to include it in the outer select statement. ? Likewise, it also ignores the "and SRVC_BGNCount >= 3" part of the Where statement.  It doesn't even give me an error, which is should becuz that part of the Where statement refers to a variable that doesn't exist in the file. @ It does not exist in the output file but it is available to SQL during the query Proc SQL;   Create Table mi.ipot2007_mi_Meas13_dedup  as     Select Distinct a.MSIS_ID          ,    a.SRVC_BGN_DT          ,   b.SRVC_BGNCount     From mi.ipot2007_mi_Meas13_CUT a          ,   (Select MSIS_ID,                   Count (Distinct SRVC_BGN_DT) as SRVC_BGNCount              From mi.ipot2007_mi_Meas13_CUT              ) b     Where a.MSIS_ID = b.MSIS_ID             and SRVC_BGNCount >= 3      Order by a.MSIS_ID         ,    a.SRVC_BGN_DT     ; Quit ; With the above version of your code you should be able to test that the count is indeed >= 3 for all records output to the table. Richard ... View more

You may not have any control over the source of the CSV, but if you do and it comes out of Excel, you could request a tab delimited file as source instead.  This is just as easy to produce from Excel, and can be read with the standard Proc Import in SAS.  The advantages are:  commas in the value are no longer a problem, no non standard code to maintain, and any additional columns which might be introduced into the data will be dealt with automatically. I would also advocate use of the comma informat. Richard ... View more

SushilKumar Create your 'Result' variable thus      Data Coded ;           Set Original ;                Result = _N_ ;      Run ; Then sort it. Richard ... View more

The code is in my reply. You need to add a data statement, a set statement, and a run statement at the end. Richard ... View more

Will this do? (NB untested code).  Count the admissions in a subquery.  If you have more than one year's data you will need to extract the year in the subquery and include it in the where conditions Proc SQL;     Create Table mi.medrecords_deduped as          Select Distinct a.ID                ,   a.ADMSN_DT          From     mi.medrecords   a                ,     (Select  ID                          ,     Count (Distinct ADMSN_DT) as Admissions                      From   mi.medrecords                     )    b           Where a.ID  = b.ID                And Admissions >= 3          Order by a.ID                ,   a.ADMSN_DT           ; Quit;  Richard ... View more

In my recollection proc SQL the input function does not generate warning messages in this situation, so the ?? is not necessary anyway.  The OP can use SQL to achieve the same result but in this instance a data step is the simplest solution. Richard ... View more

Warning messages can be suppressed using ?? Numeric = input(result, ?? best.) ; If numeric = . then character = result ; Richard ... View more

Hank First up do a quick check whether the columns giving you problems are very sparse (ie mostly missing values).  SAS checks a certain number of rows to determine the type of the data and if it only encounters null values in the first N rows it will default to one type, which may not be what you want.  I think there is an option to increase the number of rows but I have not checked. Also check whether any date columns have all cells formatted in Excel as dates (highlight the column and format the whole column, including empty cells - column names will be unaffected). One trick that may be an easy fix is to insert a new row 2 in your excel data.  In this row insert a 9 for every number column and an A for every character column, to force the import to a specific type.  Use an identical 2nd row in each table.  This row can be removed in SAS by specifying option firstobs = 2 ;. If this trick results in some values not being picked up, force the columns in question to character using this trick and use code in SAS to convert data into numeric. Assuming each table now has columns var3, var5, var9 which have been imported as character but are supposed to be numeric you can do something like this (supposing var3 should be a date).  The format specification will ensure the resulting columns are in the right order Data fulldata ;      Format var1 .... var99 Best16. ; /* Insert column names */      Format var3 ddmmyy10. ;     /* Overrides the previous format for var3 */      set _table1 (firstobs = 2 rename = (var3 = _var3 var5 =_var5 var7 = _var7))           ...           _table64 (firstobs = 2 rename = (var3 = _var3 var5 =_var5 var7 = _var7))           ;           var3 = input (_var3, ?? ddmmyy10.) ; /* ?? suppresses warning messages when data is in the wrong format */           var5 = input (_var5, comma32.) ;           var9 = input (_var9, comma32.) ;           Drop _var: ; Run ; Disclaimer: I have no way to test the above code.  But I hope you can use it Richard ... View more