sas data transformation

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

sas data transformation

Hi,

I have a sas dataset like the following.

IDENTIFIER      COMPUTATION

UNIQUE             001                        

NAME                JOHN                     

DESCRIBE        XXXX                      

DEFINE             XXXX                      

DEF001             XXXX                     

DEF002             XXXX                     

REPORT           XXXX                     

DECISION         XXXX                    

DEC001             XXXX                    

DEC002             XXXX                    

UNIQUE             002                       

NAME                GEORGE              

DESCRIBE        XXXX                    

DEFINE             XXXX                    

DEF001             XXXX                    

DEF002             XXXX                     

REPORT           XXXX                    

DECISION         XXXX                    

DEC001             XXXX                    

DEC002             XXXX                    

UNIQUE             003                       

NAME                 SAM                     

DESCRIBE         XXXX                   

DEFINE              XXXX                   

DEF001              XXXX                   

DEF002              XXXX                   

REPORT            XXXX                   

DECISION         XXXX                    

DEC001             XXXX                    

DEC002             XXXX                   

I would like create a table(one row of will contain from UNIQUE to UNIQUE) with the columns as UNIQUE, NAME, DESCRIBE, REPORT, DEFINE(concatinating DEF001, DEF002 etc), DECISION(concatinating DEC001, DEC002) and eventually upload this to sql server. The values of these columns should be assigned from COMPUATION variable.

The table should like something like the following

NAME     DESCRIBE  DEFINES                                        DECISION                                                                                               REPORT                             

JOHN      XXXX          conacatinate(def001, def002 etc)   concatinate(<B>dec001:</b>XXXX,<b><BR> dec002:</b>XXX)           XXXX

I really appreciate any ideas. Thank you.


Accepted Solutions
Solution
‎06-29-2013 01:30 AM
Super User
Super User
Posts: 7,071

Re: sas data transformation

Posted in reply to saslovethemost

data want ;

if eof then output;

set have end=eof;

length unique name $20 describe $200 report $20 define $200 decision $200 ;

retain unique -- decision ;

if identifier = 'UNIQUE' then do;

   if _N_ > 1 then output ;

   call missing (of unique -- decision);

   unique = computation;

end;

else if identifier = 'NAME' then name=computation;

else if identifier = 'DESCRIBE' then describe = computation ;

else if identifier = 'REPORT' then report=computation;

else if identifier =:'DEF' then define=catx(' ',define,computation);

else if identifier =:'DEC' then decision=catx(' ',decision,computation);

drop identifier computation ;

run;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: sas data transformation

Posted in reply to saslovethemost

First couple steps:

Add a variable to identify the group

data temp;

     set have; /*you existing data set*/

     length byvar $ 8; /* long enough to take the longest unique value*/

     retain byvar "";

     if identifier='UNIQUE' then byvar = computation;

run;

proc transbose data=temp out=transposed (drop=byvar _name_);

by byvar;

id identifier;

var computation;

run;

Should get you a data set with one record for each of your sets. IF UNIQUE is not unique this will have problems because of the by variable repeat values, If the data is not in order of the UNIQUE values also have problems with by as it will expect the by variable to be in sorted order. Sort before transpose if so.

A pass through a datastep on the transposed data with one or more of the concatenation functions (CATS CATX ) should prepare the set for the upload.    

Super User
Super User
Posts: 7,071

Re: sas data transformation

Posted in reply to saslovethemost

data step1;

set have ;

group + (identifier='UNIQUE');

run;

proc transpose data=step1 out=step2 (drop=_name_) ;

  by group ;

  id identifier;

  var computation;

run;

data want ;

  set step2;

  length decisions defines $200 ;

  decisions = catx(' ',of decision dec0Smiley Happy;

  defines = catx(' ',of define def0: );

  drop decision dec0: define def0: ;

run;

data _null_;

set want;

put (_all_) (=/);

run;

Contributor
Posts: 35

Re: sas data transformation

Thank you all for your answers. As ballardw mentioned some of the IDENTIFIER fields like DEF001, DEF001 are not unique, though the COMPUTATION is different for them. is there a way to deal this sitaution? thank you in advance.

Solution
‎06-29-2013 01:30 AM
Super User
Super User
Posts: 7,071

Re: sas data transformation

Posted in reply to saslovethemost

data want ;

if eof then output;

set have end=eof;

length unique name $20 describe $200 report $20 define $200 decision $200 ;

retain unique -- decision ;

if identifier = 'UNIQUE' then do;

   if _N_ > 1 then output ;

   call missing (of unique -- decision);

   unique = computation;

end;

else if identifier = 'NAME' then name=computation;

else if identifier = 'DESCRIBE' then describe = computation ;

else if identifier = 'REPORT' then report=computation;

else if identifier =:'DEF' then define=catx(' ',define,computation);

else if identifier =:'DEC' then decision=catx(' ',decision,computation);

drop identifier computation ;

run;

Contributor
Posts: 35

Re: sas data transformation

Thank you Tom, I really appreciate your time and effort on this, this works like a charm, one more request, I would like to add BOLD(i.e. <b>, </b>) before DEC001 and BREAK on (<br>,</br>) after its computation is added. Please refer to my above example.

Super User
Super User
Posts: 7,071

Re: sas data transformation

Posted in reply to saslovethemost

Just add some more ELSE IF clauses.  Not sure what you mean by <BR> </BR> as the break tag does not wrap around text the way that <B> </B> does. <BR /> should intruce a break.  Do you want breaks before and after?

HTML 5 &lt;b&gt; Tag

HTML 5 &lt;br&gt; Tag

else if identifier = 'DEC001' then decision=catx(' ',decision,cats('<b>',computation,'</b>'));

else if identifier =:'DEC' then decision=catx(' ',decision,cats('<br />',computation,'<br />'));


Contributor
Posts: 35

Re: sas data transformation

Yes, I need breaks before and after. You are right, <BR> should introduce a break. Thank you Tom.

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 320 views
  • 8 likes
  • 3 in conversation