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

[SAS_usercanada]

[Reeza]

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:

---

[SAS_usercanada]

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!

[Reeza]

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!

---

[ballardw]

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".

[SAS_usercanada]

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

[Reeza]

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?

[SAS_usercanada]

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

[SAS_usercanada]

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

[Tom]

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.

[Reeza]

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.

[SAS_usercanada]

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

[Reeza]

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/highlig...