Input data validation?

[deleted_user]

Hi All

I need some help in relation to bringing in data from a dataset. At column 204, the data usually has a date (YYMMDD), however on some of the records, the data has text like State St.

How do I check to see first if the input is a date nd if not, skip that record (or delete it) and read the next record into the Data step?

Thanks for your help.

Shelton.

[lu]

hi ,shelton

if name of column 204 is col_204 you can delete not valid row with:

if input(col_204,??ddmmyy8.)=. then delete;

lu

[deleted_user]

hI Lu

Do I need to put the ??ddmmyy8. in the second parameter ?

Thanks
Shelton.

[lu]

Yes,

lu

[lu]

HI ,shelton

I'm really sorry .
if your data in format is mmddyy(22072009) second parameter is ??mmddyy8.
if data format is YYMMDD(20090722) second parameter is ??yymmddn8.,

and
YYMMDD(090722) ??yymmdd6.;


lu

[sbb]

Suggest you input the field using $CHARnn. and then using the INPUT function, attempt to convert (or use the NOT ANYALPHA() function, possibly, to validate for all numeric and then perform the INPUT. Still, in case the conversion does not work, I would suggest resetting the SAS reserved variable _ERROR_=0 to avoid the nasty warning messages in the SAS log, as well, after you attempt the INPUT -- and also test the "date" numeric variable for a missing condition, which will tell you what happened with the INPUT.

You should do some diagnostic testing with the statement below - at various places in your SAS DATA step logic to better understand your input:

PUTLOG '>DIAG99' / _ALL_;

Scott Barry
SBBWorks, Inc.

[deleted_user]

unfortunately this did not work - the problem is that the value is not a blank or . but at times has some string for the EnterDate field.

[sbb]

Exactly the point - suggest reading the field into a SAS CHARACTER variable and then validate it or just run it against a suitable INFORMAT using the INPUT function creating a SAS numeric (date) variable. If the resulting SAS numeric variable value is a SAS missing value, you have an invalid date string.

Also, it's unclear what you mean by "this did not work" -- exactly what did you attempt and what was the specific result -- the SAS log with your program code pasted into a post reply would be useful in order to help diagnose the situation accurately.

Scott Barry
SBBWorks, Inc.

[deleted_user]

I tried the solution provided by Lu - this is my code:

DATA OLDORDRS ;
FORMAT LINEAMT 10.2 ;
INFILE OLDORDRS ;
INPUT @204 ENTERDT YYMMDD6. @;
IF INPUT(ENTERDT,??YYMMDD6.)= . THEN DELETE;

[deleted_user]

I tried the solution provided by Lu - this is my code:

DATA OLDORDRS ;
FORMAT LINEAMT 10.2 ;
INFILE OLDORDRS ;
INPUT @204 ENTERDT YYMMDD6. @;
IF INPUT(ENTERDT,??YYMMDD6.)= . THEN DELETE;

[deleted_user]

I was referreing to the suggestion made by Lu.

This is what my code looks like:

DATA NEWORDRS ;
FORMAT LINEAMT 10.2 ;
INFILE NEWORDRS ;
INPUT @204 ENTERDT YYMMDD6. @;
IF INPUT(ENTERDT,??YYMMDD6.)= . THEN DELETE;

[lu]

HI ,shelton

fix your code to:
DATA NEWORDRS ;
FORMAT LINEAMT 10.2 ;
INFILE NEWORDRS ;
INPUT @204 ENTERDT_TMP $6. @;
ENTERDT=INPUT(ENTERDT_TMP,??YYMMDD6.);
IF ENTERDT= . THEN DELETE;
DROP ENTERDT_TMP ; lu


Message was edited by: lu

[sbb]

Suggestion was to paste the SAS log (output) to show the SAS diagnostics or any additional PUTLOG code you would have defined to show exactly what "did not work". Pasting the code without additional benefit of SAS log processing information is limited value to get feedback.

And, as I suggested initially, consider doing the INPUT to a character variable, validate the content, using a SAS function such as ANYALPHA, and then doing the INPUT function in an assignment.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Thank you guys! It worked - I used the last solution provided by Lu and this worked - just what I was looking for.

Thanks again.

Shelton.

[Peter_C]

you can create a special user informat to detect specific strings replaced with for example special missing values, and apply the informat for the rest, with code like[pre]
proc format ;
invalue myYMD
'State St' = .S
'Deutsche' = .D
other = [yymmdd8.]
;
run;[/pre]
This informat would be used like[pre]
data ;
infile 'yourFile' ..................... truncover ;
input date_special myYMD. ;
run;[/pre]

After that you can identify the special cases and handle them however you need.

PeterC