Importind data into SAS using data step

0
votes

I have the following data:

Bellatorum School: CSULA Time: 1:40.5
The Kraken School: ASU Time: 1:45.35
Black Widow School: UoA Time: 1:33.7
Koicrete School: CSUF Time: 1:40.25
Khaos School: UNLV Time: 2:03.45
Max School: UCSD Time: 1:26.47

I want to import the above data using a SAS data step . For example, this is how I want sas data set to be:

School Name        University Time
Bellatorum School  CSULA      01:40.5

How can I do this

3
sas
How do you provide this data, in a txt-File, other database or enter it manually? - kl78
It would be helpful if you uploaded a picture of a snippet of your data. - Sean
I provide it as a Text file - Pruthvi
Can you fix the source data so that it does not use colon as the separator between the fields? That will be an issue since the time values include a colon. - Tom
Your time values are in the form X:Y.Z . Does X represent hours or minutes? Does Y represent minutes or seconds? Does Z represent seconds or does Y.Z represent seconds and fractions of seconds? If X is minutes could you have values like W:X:Y.Z where W would be hours? - Tom

3 Answers

3
votes

You can fiddle with the delimiter. I think I have the right in-format for time.

data school;
   length dlm $1;
   infile cards dlm=dlm;
   dlm=':';
   input school:$64. @;
   dlm=' ';
   input univ:$16. @;
   input @'Time:' time :hhmmss.;
   format time time12.2;
   cards;
Bellatorum School: CSULA Time: 1:40.5
The Kraken School: ASU Time: 1:45.35
Black Widow School: UoA Time: 1:33.7
Koicrete School: CSUF Time: 1:40.25
Khaos School: UNLV Time: 2:03.45
Max School: UCSD Time: 1:26.47
;;;;
   run;
proc print;
   run;

enter image description here

0
votes

You should fix your delimiters and also your TIME format. But you could read it using : as the delimiter. You just will need to remove the spurious Time string from the end of the University field. You also might want to create a real Time value from the two parts.

data want ;
  length School University $40 Minutes Seconds Time 8 ;
  infile cards dsd dlm=':' truncover ;
  input school university Minutes Seconds ;
  if scan(university,-1,' ')='Time' then
    university = substrn(university,1,length(university)-4)
  ;
  time = minutes*60 + seconds ;
  format time time11.2 ;
cards;
Bellatorum School: CSULA Time: 1:40.5
The Kraken School: ASU Time: 1:45.35
Black Widow School: UoA Time: 1:33.7
Koicrete School: CSUF Time: 1:40.25
Khaos School: UNLV Time: 2:03.45
Max School: UCSD Time: 1:26.47
;;;;
0
votes

One try would be to read whole line and then fix the columns in datastep:

data mytable (drop=mydata);
infile "D:input.txt" dlm='' truncover ;
input mydata $80.  ;
schoolname=scan(mydata,1,":");/*get the part before first :*/
University=tranwrd(scan(mydata,2,":"),"Time","");/*get part between first and second :, remove text time*/
time=catx(":",scan(mydata,3,":"),scan(mydata,4,":"));/*get part 3(hours) and 4(min+sec) and concat them*/
run;

this results in:

enter image description here

But now the time is a character-variable, if you want it as datetime you need to format it with input, something like this:

data mytable (drop=mydata);
Format time time10.;
...
...
time=input(catx(":",scan(mydata,3,":"),scan(mydata,4,":")),hhmmss.);
run;