In my text file, the data is seperated by |. So I used the code
data TEST.something;
infile something dlm = '|' dsd missover firstobs=2;
input Proc_cde : $3.
Profession_Name : $16.
Lic_id : $8.
Expire_Date : mmddyy10.
Original_Date : mmddyy10.
Rank_Code : $2.
License_Number : $5.
Status_Effective_Date: mmddyy10.
Board_action_Indicator : $1.
License_Status_Description : $5.
Last_name : $20.
First_Name : $14.
License_Active_Description : $8.
e_mail $30.
;
But in my text file, I have missing values such as ETIEXXX|MILTHYYY||||ACTIVE|23
When I print out the data, some of the values I wanted are not showed but others are correct. It also shows || in some place. The data were shifted.
So what is wrong?
EDIT
Sample data (only list a few lines).
pro_cde|Profession-Name|lic_id|Expire-Date|Original-Date|Rank-Code|License-Number|Status-Effective-Date|Board-Action-Indicator|License-Status-Description|Last-Name|First-Name|Middle-Name|Name-Suffix|Business-Name|License-Active-Status-Description|County|County-Description|Mailing-Address-Line1|Mailing-Address-line2|Mailing-Address-City|Mailing-Address-State|Mailing-Address-ZIPcode|Mailing-Address-Area-Code|Mailing-Address-Phone-Number|Mailing-Address-Phone-Extension|Practice-Location-Address-Line1|Practice-Location-Address-line2|Practice-Location-Address-City|Practice-Location-Address-State|Practice-Location-Address-ZIPcode|Email|Mod-Cdes|Prescribe-Ind|Dispensing-Ind|
732|Dental Hygienist|@2783168|03/28/2016|08/01/2005|SC|16042|08/01/2002|N|CLEAR|ETIEXXX|MILTHYYY||||ACTIVE|23|UIARI-DADE| P.O BOX 692343||MIAMI|FL|34568|815|738-4640||13830 S.W 6TH AVE||UIARI|NY|34568|[email protected]||||
732|Dental Hygienist|690|03/28/2016||CA|1143|04/20/2013|N|CLEAR|WTGD|CUIOLYN|J|||ACTIVE|56|OKALOOSA|5702 OLD BETHEL RD||CRESTVIEW|FL|32536|850|862-0924||90 RATETRACK ROAD||FORT WCLTON BEACH|VA|32547|||||
732|Dental Hygienist|9728|03/28/2016|08/27/1998|SD|11119|03/15/2012|N|CLEAR|RTED|RIHGARD|B|||ACTIVE|39|HILLSBOROUGH|249 APACHE TRAIL||BRANDON|ND|34568|313|659-7798||249 APDDHE TRAIL||BRANDON|FL|34568|||||
Your list of variables on your INPUT statement does not match your list of variables in the first line of your data. You skipped a lot columns.
You can skip columns if you want by adding some dummy variables into your INPUT statement. You must add the : modifier in front of any informats you specify in your INPUT statement to prevent SAS from reading past the delimiters.
length dummy $1 ;
drop dummy ;
input
Proc_cde : $3.
...
First_Name : $14.
3*dummy
License_Active_Description : $8.
15*dummy
e_mail :$30.
;