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

My database has a character variable that can be up to 41 spaces long. For example, "DISCHARGED, DID NOT RETURN FROM COURT" or "TRANSFER TO A MEDICAL HOSPITAL". I specified this length for the variable when I imported the original data into SAS using this statement in the infile step: 

@43 MvmtDesc :$41.

When I do proc contents, I can see that the variable length is 41: 

MvmtDesc Char 41

HOWEVER, when I print the variable or look at in in the work library, I can only see "DISCHARGED" and "TRANSFER", not the whole variable length (by this I mean that I cannot see the full phrase). It seems like the rest of the variable value is getting suppressed or truncated at the commas and spaces. Is there a way to fix this so that I can see the whole variable value? Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

It was probably a mistake to add the colon to the INPUT statement ... in every case where the colon appears.  The colon instructs SAS to read the data in "scanning from left to right" mode meaning it will stop when it hits a blank.  So you have observed one of the side effects, getting the first word only for MVMTDESC.  But there can be other side effects as well.  For example, suppose a variable is missing from the incoming line of data.  The colon tells SAS to read across from left to right, and find something.  It might take a piece of the next variable instead.

 

There are cases where a colon is required, but typically that occurs when the incoming data fields do not line up.  Removing the colons should be the easy solution (as long as the data values line up from line to line).

 

There is another tool that you can experiment with, for the final variable only.  Change the colon to an ampersand (& instead of : ).  That still tells SAS to scan from left to right, but doesn't stop until it hits two blanks in a row.

View solution in original post

7 REPLIES 7
jklaverstijn
Rhodochrosite | Level 12

The way SAS behaves is as documented. You use the colon modifier on the input statement. This is what the doc states:

 

SAS reads the value from the next non-blank
column until the pointer reaches the next blank column, or the end of
the data line, which ever comes first.

I would suggest using another separator between the fields. Eg. use a comma and specify the input file as follows:

 

data have;
   infile indata dsd;
   input ...
run;

I hope you can influence the shape of that input file.

 

Hope this helps,

- Jan.

 

 

Kels123
Quartz | Level 8

Hi Jan,

Thank you for your response. This was the code I used to import the data from the original text file:

data data.mvmt;

infile "J:\Kelsey data... ETC...\YALEMV.TXT" dlm=' ' ;    *I had to use this dlm because this is how the text file that was given to me 

input @1 InmateNo $8.                                                     separated the variables*;

@10 MvmtDate mmddyy10.

@21 MvmtCode :$7.

@29 ReceivingFac :$3.

@33 SendingFac :$3.

@37 JurisCode :$3.

@41 MvmtStatus :$1.

@43 MvmtDesc :$41.                                                     *This last variable is the variable I am having trouble with*;

;

run;

 

Are you saying that even though I specify the length of 41 for this variable, SAS will still truncate it at the next space? Unfortunately modifying the data is not an option, so I am stuck working with a text file that uses spaces both BETWEEN variables and WITHIN variables. Is there no solution to override the WITHIN variable spaces? Thank you.

jklaverstijn
Rhodochrosite | Level 12

@Kels123 wrote:...so I am stuck working with a text file that uses spaces both BETWEEN variables and WITHIN variables. Is there no solution to override the WITHIN variable spaces? Thank you.

 

Basically there is no way of knowing for certain if a space indicates the next field or is part of a value. Your data is ambigious. So the answer is no.

 

- Jan.

Tom
Super User Tom
Super User

Since you are using pointer control to tell SAS where to read the data you do NOT want either the colon ( : ) modifiers on your INPUT statement or the DLM option on your INFILE statement.  You probably DO want to add the TRUNCOVER option to your INFILE statement so that you can read lines that are shorter than 83 characters long without errors.  Also make sure to apply a format to your date variable so that humans can understand the values when you print them.

 

data data.mvmt;
  infile "J:\Kelsey data... ETC...\YALEMV.TXT"  TRUNCOVER ;
  input 
  @1 InmateNo $8.
  @10 MvmtDate mmddyy10.
  @21 MvmtCode $7.
  @29 ReceivingFac $3.
  @33 SendingFac $3.
  @37 JurisCode $3.
  @41 MvmtStatus $1.
  @43 MvmtDesc $41.
  ;
  format MvmtDate DATE9.;
run;

You could also just tell SAS what columns to read instead of using @ pointer controls.  However to get it to use the MMDDYY informat you will need to use the either the @ 10 pointer control or +1 pointer control to skip the empty column 9.

data data.mvmt;
  infile "J:\Kelsey data... ETC...\YALEMV.TXT"  TRUNCOVER ;
  input 
    InmateNo 1-8
    +1 MvmtDate mmddyy10.
    MvmtCode 21-27
    ReceivingFac 29-31
    SendingFac 33-35
    JurisCode 37-39
    MvmtStatus 41
    MvmtDesc 43-83
  ;
  format MvmtDate DATE9.;
run;

 

Astounding
PROC Star

It was probably a mistake to add the colon to the INPUT statement ... in every case where the colon appears.  The colon instructs SAS to read the data in "scanning from left to right" mode meaning it will stop when it hits a blank.  So you have observed one of the side effects, getting the first word only for MVMTDESC.  But there can be other side effects as well.  For example, suppose a variable is missing from the incoming line of data.  The colon tells SAS to read across from left to right, and find something.  It might take a piece of the next variable instead.

 

There are cases where a colon is required, but typically that occurs when the incoming data fields do not line up.  Removing the colons should be the easy solution (as long as the data values line up from line to line).

 

There is another tool that you can experiment with, for the final variable only.  Change the colon to an ampersand (& instead of : ).  That still tells SAS to scan from left to right, but doesn't stop until it hits two blanks in a row.

jklaverstijn
Rhodochrosite | Level 12

This may actually work, especially as the variable in question is at the end of the record. Otherwise I would not see this works as the data does not contain these two consecutive spaces. But kudos for finding an escape,

 

Regards,

- Jan.

Kels123
Quartz | Level 8

Thank you- this solved my problem! It turns out that the colon was actually the issue, but thank you for sharing the idea about using the ampersand. This may come in handy in the future. Thank you again!

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 1823 views
  • 1 like
  • 4 in conversation