@BruceTao wrote:
Hi,
Getting close.
Removing the quotes from the source system worked except for one field.
It changed the value to numeric but it should of been a character. Just briefly looking at the field, it might be that most of, if not all the values are numeric.
How can I change that one field to character?
Thanks
Bruce
You cannot force PROC IMPORT to that. The problem is that the only metadata that a delimited file contains is the header row. There is no place to encode any information about the variable types, lengths, formats, etc.
If you can create an XLSX file instead then SAS could know what data type was used for the cells.
Note that there isn't really any need to use PROC IMPORT to read a delimited file as it is almost as easy to write the data step code to read it as it is to write the code to run PROC IMPORT. When you do that you will have complete control over how to define the variables.
What I normally do is just copy the header row and turn it into a LENGTH statement. If you are not sure how to define the variable just make it as a long character string and then examine the results. You can always just fix the program and re-run it. Then I will add INFORMAT and/or FORMAT statement for those variables that REQUIRE them. Most variables do NOT require either. But values like DATE, TIME and DATETIME values do need informats to read the text into value properly and FORMATS to display the values as text in a human recognizable way. In your data you might want to use the TRAILSGN informat to read those values with the negative sign on the end.
data my_data;
infile sample dsd dlm='|' truncover firstobs=2;
length
Action $50 ObjValue $12 User $8 Date 8 Time 8 ChangeID $3 FieldName $32 ShortText $50
OldValue 8 NewValue 8 COUNT 8 CC_STAFF_FLAG 8
;
input Action -- CC_STAFF_FLAG;
informat date yymmdd. time time. oldvalue newvalue trailsgn.;
format date yymmdd10. time time8.;
run;
Obs Action ObjValue User Date Time
1 Minimum interest rate (interface only) Changed 0023175086 E77319 2017-07-12 11:25:11
2 Interest Settlement Frequency (interfac) Changed 0023175086 E77319 2017-07-12 11:25:11
3 Amount Determ. Category (interface) Changed 0023175086 E77319 2017-07-12 11:25:11
4 Total Monthly Payment Changed 0023175086 E77319 2017-07-12 11:25:11
Change Field Old New CC_STAFF_
Obs ID Name ShortText Value Value COUNT FLAG
1 U ZZN40024 Minimum interest rate (interface only) 0 -999.00 1 0
2 U ZZN40029 Interest Settlement Frequency (interfac) . 0.00 1 0
3 U ZZN40030 Amount Determ. Category (interface) . 30.00 1 0
4 U ZZN80014 Total Monthly Payment 0 405.09 1 0
... View more
