Desktop productivity for business analysts and programmers

Using EG to transform Semi Structured Log Data

Accepted Solution Solved
Reply
SAS Employee
Posts: 2
Accepted Solution

Using EG to transform Semi Structured Log Data

Hi, I have a data set in CSV that looks like the following. T1 - T29 are actually the column names for the data columns that follows at the bottom. 

 

semistructured.JPG

 

How do I use SAS EG to assign the column names (T1, T2, T3, etc) to the columns that i extracted using the import wizard as seen below, instead of the default column names F1, F2, etc?importeddata.JPG

 

Appreciate any input! thanks very much

 

Regards

Nat


Accepted Solutions
Solution
‎08-14-2016 09:09 PM
Grand Advisor
Posts: 9,596

Re: Using EG to transform Semi Structured Log Data

If you can make a MAP file to display how to rename them, That would be easy.


data map;
input old $ new $;
cards;
F1 T1
F2 T2
;
run;

data _null_;
 set map end=last;
 if _n_=1 then call execute('proc datasets lib=work nolist nodetails;modify have;rename ');
 call execute(catx('=',old,new));
 if last then call execute(';quit;');
run;


View solution in original post


All Replies
Grand Advisor
Posts: 9,596

Re: Using EG to transform Semi Structured Log Data

If you know the exactly number of variables, that would be easy.

data want;
 set have(rename=(F1-F29=T1-T29));
run;



SAS Employee
Posts: 2

Re: Using EG to transform Semi Structured Log Data

Thanks but the column names are actually the ones hidden in black (for confidentiality purposes), so its not as easy as "T1" and "T2" etc. It looks something like P1TU45S and Q_24FTG, etc.

 

I tried to import that column separately and transpose so I would have a table with a single row where the column name is the same as the data in the first row. I thought it would work if I appended these two tables but it didnt.. furthermore the formats don't match (i.e. column name table has all characters and data table is numeric, hence no match).

 

Any suggestions?

 

Regards,

Nat

Solution
‎08-14-2016 09:09 PM
Grand Advisor
Posts: 9,596

Re: Using EG to transform Semi Structured Log Data

If you can make a MAP file to display how to rename them, That would be easy.


data map;
input old $ new $;
cards;
F1 T1
F2 T2
;
run;

data _null_;
 set map end=last;
 if _n_=1 then call execute('proc datasets lib=work nolist nodetails;modify have;rename ');
 call execute(catx('=',old,new));
 if last then call execute(';quit;');
run;


☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 208 views
  • 0 likes
  • 2 in conversation