BookmarkSubscribeRSS Feed
Longimanus
Obsidian | Level 7

Hi all! I hope somebody can help me with this 

 

I have some .skv. files that I need to covert. The files consist fo three record types each, semicolon separated. 

E.g.:

 

01;2017-03-31;header;just some text;;

02;2017-03-31;Vanilla;0.00;2L;36148011111111;99991231;+;1999.50;;

02;2017-03-31;Pistache;150.75;5L;36148012345678;99991231;-;157.32;;

09;2017-03-31;trailer;how do I do this;;

 

No need to mention the above is made up.

 

I need to convert the numbers that are underlined. Then I need it all to be copied back (using proc export) and it the rest has look EXACTLY the same.  My problem is that the bold values are seen by PROC IMPORT as numeric values and therefore 0.00 is stored as 0 in a numeric field.  The record type (the first two chars) are also tagged numeric and contain: 1, 2 and 9. But I don't need this. I NEED them to stay 01, 02 etc and 0.00 should stay 0.00.  I do not want to ping-pong with the vars from char to num and back to char.  Is there not an easy way where I can just get all the variables in (different output for the three records types of course) and tell PROC IMORT : "HEY ... I want them ALL in as CHAR. Do not try to fix anything. 

 

I want this in my table for the 02 record typeM; all character. 

VAR1 = 02

VAR2 = 2017-03-31

VAR3 = Vanilla

VAR4 = 0.00

VAR5 = '2L'

VAR6 = '36148011111111'

 

Then I want to convert VAR6 to 123456789 and then I want to write it back to a new .skv file looking like: 

 02;2017-03-31;Vanilla;0.00;2L;123456789;99991231;+;1999.50;;

 

How do I do this? 🙂

 

I do not want to do special things to make the record-type 02 again after proc import has changed it to 2.  The same for 0 that should be 0.00.

 

I hope I've explained in a way so you "guys" know that I want.  I am almost certain this is just an option I have to choose somewhere but I haven't been able to find it.

 

Best regards,

Menno 

10 REPLIES 10
Reeza
Super User

'Guys'? If you have to quote it use a different word. 

 

There isn't that option in Proc Import to read all variables as characters. 

Write a data step to customize the import. 

Longimanus
Obsidian | Level 7

With "guys" I just wanted to emphasize that I address both sexes. Not that we should start a topic on semantics here.  But thanx for that advise. 

 

So know I know there is no option that can force proc import to store all as characters. I need to write a datastep. Can I mention here that I tried and did not succeed or does that mean I'm not worthy to post a question here? /BR Longimanus

Tom
Super User Tom
Super User

If you don't like the way that the algorithm that PROC IMPORT uses to guess at the data types works for your data then don't use PROC IMPORT. There is no need to use PROC IMPORT to read a text file. You can probably almost end up writing less code to read it using a DATA step than it took to write the PROC IMPORT call.  Plus you can add logic to only read the '02' records.  Or read the different records in different ways.

 

data want;
  length var1 $2 var2 8 var3 $20 var4 8 var5 $5 var6 $14;
  infile 'myfile.skv' dsd dlm=';' truncover ;
  input @;
  if _infile_=: '02' ;
  informat var2 yymmdd.;
  format var2 yymmdd10.;
  input var1-var6;
run;

 

Not sure if you need to read the '01' and '09' records.  Do they appear only at the start and end of the file?  or do they appear multiple times and indicate some type of grouping?  Any way it is easy to write a data step to write the data once you have it in a data step.

data _null_;
  set have  end=eof;
  file 'new.skv' dsd dlm=';' ;
  if _n_=1 then put '01;header;sometext';
  put var1-var6 ;
  if eof then put '09;footer;otherstuff';
run;
Longimanus
Obsidian | Level 7

Hey Tom!

 

Thank you. I try to mould what you suggested into something I can use. Let's see if I can make it work. I do like PROC IMPORT but just for this one I wish there was an option to get all char data types. I have a time issue (a lack of it) so I was hoping an easy way out. Writing a data step that can do this is of course for some of you simple. It's not what I do on a daily basis. But thank you for the input. I might come back 🙂 /BR Longimanus

Tom
Super User Tom
Super User

If the purpose is not actually use the data, but just make a change to the one value of one variable then read them all as character.

It looks like the '02' records have 11 fields and the the other two just 6.

 

data _null_;
  infile 'oldfile.skv' dsd dlm=';' truncover ;
  file 'newfile.skv' dsd dlm=';' ;
  length x1-x11 $200 ;
  input x1-x11 ;
  if x1='02' then x6='123456789';
  if x1='02' then put x1-x11 ;
  else put x1-x6;
run;
Longimanus
Obsidian | Level 7

Tom there is a header and a trailer.  It's not for grouping. These two rows I will not touch. I  simply copy them  when I got the rows I need to convert in place. Header at the start, trailer at the end. In between those I have 3 differents types of rows. In one of them I need to convert that 3614* number I mentioned. Again, thanx for the input ... time to try it out. 

Kurt_Bremser
Super User

Reading your example data into character variables looks like that:

data want;
infile datalines4 dlm=';' truncover;
input
  var1 :$2.
  var2 :$10.
  var3 :20.
  var4 :$20.
  var5 :$2.
  var6 :$20.
  var7 :$8.
  var8 :$1.
  var9 :$10.
  var10 :$5.
;
datalines4;
01;2017-03-31;header;just some text;;
02;2017-03-31;Vanilla;0.00;2L;36148011111111;99991231;+;1999.50;;
02;2017-03-31;Pistache;150.75;5L;36148012345678;99991231;-;157.32;;
09;2017-03-31;trailer;how do I do this;;
;;;;
run;

Just remove the part from datalines4; to ;;;; and replace the datalines4 in the infile statement with your filename.

Reeza
Super User

 

Allright lady's*, you have a text file that you're trying to read. It has mixed types but you want all as character. 

 

Within a data step and INFILE statement there are two options that can help you out:

 

1. FIRSTOBS -> set it to the 2nd row so the header doesn't interfere with your data.

2. EOV/EOF/END -> indicates the last record and you can not read/output this line if desired.

3. Read the entire line as a single variable and then parse out the remaining variables. If you only have a few and they follow a pattern you can use a SCAN function to seprate the components and keep them as characters. 

4. When you use PROC IMPORT with a text file it should generate the code behind the scene in the log. Usually you can take this and modify it as necessary. 

 

There's a ballotware item to add the option to read all as character in PROC IMPORT here, please upv...

 

*If you can't replace guys with ladies and write that then don't use it in the first place. 

 

 

 

Longimanus
Obsidian | Level 7

Reeza ..... I highly appreciate the advice you are given on my SAS problem. Serioulsy. But that other thing. Jeeeez. 

 

Who needs a course in avoidng stereotypes and sexist remarks? You or me? "GUYS" can be used combined (am I really trying to explain this?) for BOTH sexes. I just liked in a fun way to emphasize there are a LOT of proffesional women out there. SOME male living-with.their-mum programmers might think good advice only comes from there own gender. The same with the oppsite species living with her parents. Get pissed about I just wrote GUYS. So now I pleased them but I stepped on you toes. Apparently you cannot please everbody. Anyway .. I feel silly to point this all out. I will not address it further eventhough I have a feeling you want to take this to another level. 😉

 

But again, the advice you gave to solve my problem is priceless. Thank you!

 

Cheers!

Longimanus 

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
  • 10 replies
  • 1094 views
  • 4 likes
  • 4 in conversation