BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Murali11
Obsidian | Level 7

Hi everyone,

Good afternoon. I was trying to resolve the missing date issue as shown in the name "Masters T."  27. I tried several options including dlm dsd missover, and finally the if then statement worked. However, the log still shows:

 

NOTE: Invalid data for Date in line 98 14-20.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
98 "Masters,T." 27
Name=Masters,T. Date=. Amt=27 _ERROR_=1 _N_=5
NOTE: The data set WORK.AMOUNTS has 6 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.10 seconds
cpu time 0.06 seconds

 

Programmatically, what is the correct way to address the missing date value? Please advise. Thank you very much for your review and help.

 

data clients;

infile datalines dsd dlm=' ' MISSOVER;
length Name $15.;
input Name $ EmpID $;
datalines;
"Ankerton,L." 11123
"Davis,R." 22298
"Masters,T." 33351
"Womer,B." 44483
;
data Amounts;
infile datalines dsd dlm=' ' MISSOVER;
length Name $15.;
input Name $
Date date7.
Amt dollar3.;
if Date=' ' then Amt='27';
datalines;
"Ankerton,L." 08OCT96 92
"Ankerton,L." 15Oct96 43
"Davis,R." 04Oct96 16
"Masters,T." 13Oct96 18
"Masters,T."   27
"Thomas,A." 21Oct96 15
;
proc sort data=clients;
by Name;
run;
proc sort data=Amounts;
by Name;
run;
data clientsamtcomb;
merge clients Amounts;
BY Name;
run;
proc print data=clientsamtcomb;
format date date7. Amt dollar3.;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

I suggest that you use:

 

data Amounts;
infile datalines dsd dlm=' ' TRUNCOVER;
length Name $15;
input 
    Name $ 
    Date ?? :date.
    Amt dollar10.; 
format date yymmdd.;
datalines;
"Ankerton,L." 08OCT96 92
"Ankerton,L." 15Oct96 43
"Davis,R." 04Oct96 16
"Masters,T." 13Oct96 18
"Masters,T."   27
"Thomas,A." 21Oct96 15
;
PG

View solution in original post

4 REPLIES 4
Astounding
PROC Star

The right way to refer to missing values:

 

if date = . then amount = 27;

 

On a side note, you may need to increase the format width for printing AMOUNT.  If AMOUNT if 110 (which could be read with the DOLLAR3. informat in your top DATA step), you would still need 4 characters (not 3) to print out the value as $110.  So the FORMAT statement in the final PROC PRINT should use:

 

format amount dollar4.;

 

To get back to your original question, the right way to address the data problem is to fix the data.  Try adding an extra blank in the data in place of the missing date.  Then the combination of DSD and DLM=' ' should be able to figure out what belongs where.  No guarantees on that, but worth a try.

mkeintz
PROC Star

When the date is missing, you see that AMT is not read in.  In your example the number 27 is read with informat DATE7. and fails, and also AMT is set to missing.  So you put in this fix:

   if date=' ' then amt=27;

which has 2 problems

  1. The expression IF DATE=' ' compares the character value " " to the numeric variable DATE.  You should use:
        IF DATE=.
    which will eliminate the "Character values have been converted to numeric values" note

  2. More importantly you're assigning the value 27, which works great if DATE is missing only when the intended AMT is 27.  You need a more general correction:
data amounts;
  infile datalines dsd dlm=' ' MISSOVER;
  length Name $15.;
  input Name $    Date date7.    Amt dollar3.  ;
  format date date9.;
  if date=. and amt=. then amt=input(scan(_infile_,-1,' '),best32.);
datalines;
"Ankerton,L." 08OCT96 92
"Ankerton,L." 15Oct96 43
"Davis,R." 04Oct96 16
"Masters,T." 13Oct96 18
"Masters,T."   27
"Thomas,A." 21Oct96 15
run;

 

This assumes that AMT is always present and is always the last space-separated term in the input line (which is referred to via the automatic variable _INFILE.  The SCAN function gets the "-1" term (i.e. the last term - first from the end).

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
PGStats
Opal | Level 21

I suggest that you use:

 

data Amounts;
infile datalines dsd dlm=' ' TRUNCOVER;
length Name $15;
input 
    Name $ 
    Date ?? :date.
    Amt dollar10.; 
format date yymmdd.;
datalines;
"Ankerton,L." 08OCT96 92
"Ankerton,L." 15Oct96 43
"Davis,R." 04Oct96 16
"Masters,T." 13Oct96 18
"Masters,T."   27
"Thomas,A." 21Oct96 15
;
PG
Murali11
Obsidian | Level 7

Hi PGStats,

 

Your solution worked really well as it removed the error that I had received when trying with the "IF ...THEN" statement. Thank you so much for your timely help. I appreciate it. The fact that the combination of DSD and MISSOVER or individually on their own should have worked and not sure why they did not work. 

 

Thanks again,

Murali

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 11310 views
  • 4 likes
  • 4 in conversation