SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Importing Data from a heirarchical data delimited format data set to SAS

Reply
Contributor
Posts: 32

Importing Data from a heirarchical data delimited format data set to SAS

I have a heirarchcical delimited data set that I need to import. Data are comma delimited. The first vaobservation in the heirarchy is a line indicating the number of lines is as follows:

 

132,1001,CB1TF,1000,1000,1

 

The first varialbe is the number of lines following the first line that are assigned to the group, the second is the number of cells or volumetric partitions in the group (variables x lines), third is the group name, and the last three give information concerning size (width, length, depth) of the group,

 

The next set of lines look like this with each line representing a specific cell at the location as specified by the first two numbers, followed by the number of depths and followed thereafter with measurments and each of thoses depths.

 

375000,4311000,2,13.07,12.78

The data cotinue like this ...

376000,4311000,8,13.08,12.79,12.63,12.55,12.40,12.26,12.02,11.79
377000,4311000,12,13.10,12.80,12.64,12.57,12.41,12.26,12.02,11.77,11.43,11.13,11.00,10.88
378000,4311000,14,13.11,12.81,12.65,12.58,12.42,12.26,12.01,11.75,11.39,11.08,10.96,10.83,10.59,10.40
379000,4311000,18,13.11,12.82,12.66,12.59,12.43,12.27,12.00,11.73,11.36,11.04,10.91,10.79,10.55,10.37,10.31,10.19,10.07,9.95
380000,4311000,26,13.12,12.83,12.66,12.60,12.43,12.27,12.00,11.71,11.33,11.00,10.87,10.75,10.52,10.35,10.27,10.15,10.04,9.92,9.82,9.74,9.67,9.47,10.18,10.07,9.99,9.96

....

 

until you reach the 132 specified in the first line at which point you find another line demarcating another group as for example

 

270,1002,CB2OH,1000,1000,1

This pattern repeats for the entire file. There is no EOF marker I need to retain the group information for each line of data at least until I can sum the data up. But first I have import the data. Can anyone suggest example code?

Super User
Super User
Posts: 7,981

Re: Importing Data from a heirarchical data delimited format data set to SAS

Well, there's no simple solution there I am afraid.  Personally I would not have accepted this for data in the first place.  What I would suggest is this:

1)  Import the datafile using a datastep + infile and read the whole string each time into one variable, call it string:

data have;  

  infile "xyz.txt";

  length string $2000;

  input string $;

run;

 

2) Next write a datastep which starts by blocking the data out into sections:

data inter (drop=num);
  set have;
  retain num block;
  if _n_=1 then do;
    num=input(scan(string,1,","),best.);
    block =1;

    header_record="Y";
  end;
  else do;
    num=num-1;
    if num < 0 then do;
      block=block+1;

      header_record="Y";
      num=input(scan(string,1,","),best.);
    end;
  end;
run;

 

Now you have an id for each group and can process each row of data out into your variables by using scan, header record is shown with the Y so you can pull that out as well.

Ask a Question
Discussion stats
  • 1 reply
  • 199 views
  • 0 likes
  • 2 in conversation