DATA Step, Macro, Functions and more

Input data validation?

Reply
N/A
Posts: 0

Input data validation?

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.
Contributor lu
Contributor
Posts: 23

Re: Input data validation?

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: Input data validation?

hI Lu

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

Thanks
Shelton.
Contributor lu
Contributor
Posts: 23

Re: Input data validation?

Posted in reply to deleted_user
Yes,

lu
Contributor lu
Contributor
Posts: 23

Re: Input data validation?

Posted in reply to deleted_user
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
Super Contributor
Super Contributor
Posts: 3,174

Re: Input data validation?

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.
N/A
Posts: 0

Re: Input data validation?

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: Input data validation?

Posted in reply to deleted_user
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.
N/A
Posts: 0

Re: Input data validation?

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;
N/A
Posts: 0

Re: Input data validation?

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;
N/A
Posts: 0

Re: Input data validation?

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;
Contributor lu
Contributor
Posts: 23

Re: Input data validation?

Posted in reply to deleted_user
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
Super Contributor
Super Contributor
Posts: 3,174

Re: Input data validation?

Posted in reply to deleted_user
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.
N/A
Posts: 0

Re: Input data validation?

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.
Valued Guide
Posts: 2,177

Re: Input data validation?

Posted in reply to deleted_user
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
Ask a Question
Discussion stats
  • 14 replies
  • 214 views
  • 0 likes
  • 4 in conversation