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:
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!
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!
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".
if several new variables are added to the dataset, I need to keep all these records.
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
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.