topic Re: How to convert a hierarchy file into a flat file in SAS? in New SAS User

How to convert a hierarchy file into a flat file in SAS?

SAS_usercanada — Thu, 18 Apr 2019 19:11:02 GMT

Re: How to convert a hierarchy file into a flat file in SAS?

Reeza — Thu, 18 Apr 2019 19:50:39 GMT

1. Import the data set

2. I would start by creating an ID field first to know which rows go together.

Then combine them.

*add IDNUBMER to each row;

data import_Add_ID;
set importData;

retain ID;
if not missing(IDNumber) then ID=IDNumber;

run;

And to combine them now:

data want;
update import_add_ID (obs=0) import_add_ID;
by ID; *this is new id variable created in previous step;
run;

Hope that gets you started. If you have further questions please post back.

FYI - I'm assuming this is actually an Excel file. If it's JSON or XML there are better options, but since you said flat file, I'm assuming a text file that Excel is reading.

@SAS_usercanada wrote:

Re: How to convert a hierarchy file into a flat file in SAS?

SAS_usercanada — Thu, 18 Apr 2019 20:08:09 GMT

if one more variable is added at the end of the old dataset, this new variable can have several lines with different value for the same IDNumber, how to keep those records for the same IDNumber.

Thank you!

Re: How to convert a hierarchy file into a flat file in SAS?

Reeza — Thu, 18 Apr 2019 20:46:29 GMT

I don't know what that means. Can you please illustrate that with some data - preferably as text.

@SAS_usercanada wrote:

if one more variable is added at the end of the old dataset, this new variable can have several lines with different value for the same IDNumber, how to keep those records for the same IDNumber.

Thank you!

Re: How to convert a hierarchy file into a flat file in SAS?

ballardw — Thu, 18 Apr 2019 22:14:00 GMT

If you post an example of the data as TEXT, assuming your data starts as text, we might be able to provide working code.

Copy enough lines of the data to show 3 or 4 sets of records, then on the forum open a code box using either the {I} or the "running man" icons on the menu bar. This box is very important as the main message window will reformat text making it not the same as your source file.

Or if your data is in a spread sheet you might try saving to a text file with SPACE as a delimiter.

Unless your file has differing numbers of rows per record then this should not be a problem.

And example of reading a TEXT file with random numbers of spaces before the values:

data example ;
   input idnumber :$10. 
         /lastname :$15.
         /firstname :$15.
         /dateofbirth :yymmdd10.
         /gender
         /maritalstatus
         /dependants
   ;
   format dateofbirth date9.;
datalines;
ASAAFFF
     Anderson
          Rose
              1947-04-30
                 1
                    1
                        99
;
run;

The / in the input statement says to "read on the next line".

Re: How to convert a hierarchy file into a flat file in SAS?

SAS_usercanada — Tue, 23 Apr 2019 13:42:50 GMT

if several new variables are added to the dataset, I need to keep all these records.

Re: How to convert a hierarchy file into a flat file in SAS?

Reeza — Tue, 23 Apr 2019 14:43:37 GMT

Text please, not images and this is still unclear. How would you like that data to be shown? Show exactly what you have to start with and what you want as output. Is it a new record for each line? Is it the max/min of certain fields?

Re: How to convert a hierarchy file into a flat file in SAS?

SAS_usercanada — Tue, 23 Apr 2019 15:28:14 GMT

the data should show as a flat file. if there is a new taskid, a new record for the same IDNumber should be created.

IDNumber lastName firstName dateOfBirth gender maritalStatus numberOfDependantChildren taskidID taskTitle taskCode taskStartDate taskEndDate
AAAA708 Anderson Ross   1944-04-30 1 1  99    262234   JobSearch1 111 2019-01-28 2019-01-28
AAAA708 Anderson Ross   1944-04-30 1 1  99    262231  JobSearch1 111 2019-01-31 2019-01-31
BBBB708 Moulton Ronda   1947-03-04 2 1  99    249335  JobSearch2 102 2018-08-02 2018-08-31
BBBB708 Moulton Ronda   1947-03-04 2 1  99    249241  JobSearch2 102 2018-09-02 2018-09-22
BBBB708 Moulton Ronda   1947-03-04 2 1  99    249395  JobSearch3 135 2018-08-02 2018-08-31

Re: How to convert a hierarchy file into a flat file in SAS?

SAS_usercanada — Tue, 23 Apr 2019 15:33:49 GMT

The source file is XML. When I open it in Excel, the data format is not easy to read.

IDNumber lastName firstName dateOfBirth gender maritalStatus numberOfDependantChildren taskidID taskTitle taskCode taskStartDate taskEndDate
AAAA708
Anderson
  Ross
   1944-04-30
    1
     1
      99
       262234 JobSearch1 111 2019-01-28 2019-01-28
       262231 JobSearch1 111 2019-01-31 2019-01-31
BBBB708
Moulton
  Ronda
   1947-03-04
    2
     1
      99
       249335 JobSearch2 102 2018-08-02 2018-08-31
       249241 JobSearch2 102 2018-09-02 2018-09-22
       249395 JobSearch3 135 2018-08-02 2018-08-31

Re: How to convert a hierarchy file into a flat file in SAS?

Reeza — Tue, 23 Apr 2019 15:58:37 GMT

Have you tried using SAS XML mapper to read the file first? Or the XML libname? It will come in separate tables and you'll need to merge them, but should be pretty straightforward.

Re: How to convert a hierarchy file into a flat file in SAS?

SAS_usercanada — Tue, 23 Apr 2019 16:18:17 GMT

We are unable to install SAS XML mapper. So I have to use programing to change the data format.

Re: How to convert a hierarchy file into a flat file in SAS?

Reeza — Tue, 23 Apr 2019 17:39:01 GMT

Libname option is still there. What version of SAS are you using? You can find it using the following:

proc product_status;run;

This is a quick example of how to read that file:
https://communities.sas.com/t5/SAS-Programming/How-can-I-import-XML-file-into-SAS/m-p/283948/highlight/true#M57913

Re: How to convert a hierarchy file into a flat file in SAS?

Tom — Tue, 23 Apr 2019 20:03:06 GMT

Don't open the XML file in Excel. Open it in a text editor and copy some of the lines and paste them into pop-up for loading text (see the {i} icon on menu)..

Or attach it hear as a text file.