DATA Step, Macro, Functions and more

is it possible to "unflatten" files using SAS?

Reply
Contributor K_S
Contributor
Posts: 25

is it possible to "unflatten" files using SAS?

We can easily create flat files using proc transpose or by using arrays. 

What I am trying to do is to "unflatten" a file that is currently flat.

Is it possible? 

Super User
Posts: 5,427

Re: is it possible to "unflatten" files using SAS?

In most cases yes. But it depends on the nature of your data.
Please exemplify with sample have and want data sets.
Data never sleeps
Contributor K_S
Contributor
Posts: 25

Re: is it possible to "unflatten" files using SAS?

So what I have looks like this....

participant IDcontact 1contact 2type of relationship 1

type of

relationship 2

gender of contact 1gender of contact 2
1KSRS11MF
2LBGM21FF
3JRDD22MM

 

 

and I want to make it look like this...

Participant IDContacttype of relationshipgender
1KS1M
1RS1F
2LB2F
2GM1F
3JR2M
3DD2M

 

 

 

Super User
Posts: 5,503

Re: is it possible to "unflatten" files using SAS?

Also note that PROC TRANSPOSE can work in both directions:  wide to narrow, and narrow to wide.

Contributor K_S
Contributor
Posts: 25

Re: is it possible to "unflatten" files using SAS?

Posted in reply to Astounding

yes, that is probably the way to go. I posted what I have and what I want to end up with in reply to another poster. Would be great if you could look at it and suggest a code to use. 

Thanks in advance Smiley Happy

Super User
Posts: 5,503

Re: is it possible to "unflatten" files using SAS?

In that post, is the sample data your actual variables, or just representative of your actual variables?  If it's just representative, how is the actual data different?  There would be "many more" of something, but it's difficult to tell what.

Contributor K_S
Contributor
Posts: 25

Re: is it possible to "unflatten" files using SAS?

[ Edited ]
Posted in reply to Astounding

Nope, not my actual variables or data (REB wouldn't be happy about that!).

My actual dataset looks just like the example I provided, but it is 'wider'.

I have ~ 250 participants and then I have 10 contacts per participant and all kinds of data about the relationship between the participants and each one of their 10 contacts. Does this help?

 

Trusted Advisor
Posts: 1,560

Re: is it possible to "unflatten" files using SAS?

Having only two contacts in a row can be held by next code.

If the are more contacts in a row you can use either arrays or a macro like demostrated:

 

Without a macro:

data have;
input part_ID   
      contact_1  $     contact_2 $
      type_of_rel_1    type_of_rel_2
      gender_1   $     gender_2  $ ;
datalines;
1   KS  RS  1   1   M   F
2   LB  GM  2   1   F   F
3   JR  DD  2   2   M   M
; run;

data want(keep=part_id contact type_of_rel gender);
 set have;
     contact = contact_1;
     type_of_rel = type_of_rel_1;
     gender = gender_1;
     output;
     
     contact = contact_2;
     type_of_rel = type_of_rel_2;
     gender = gender_2;
     output;         
run;     

Using a macro:

 %macro out(n);    
    contact = contact_&n;
     type_of_rel = type_of_rel_&n;
     gender = gender_&n;
     output;   
%mend out;
data want(keep=part_id contact type_of_rel gender); set have;
%out(1);
%out(2);
run;

 

  You can even do last step  dynamical:

 

%macro doit(loop);
data want(keep=part_id contact type_of_rel gender);
 set have;
      %do i=1 %to &loop;
         %out(&i);
      %end;
run;
%mend doit;
%doit(2);   /* 2 for two contact per row */
Super User
Posts: 5,503

Re: is it possible to "unflatten" files using SAS?

You have several good suggestions so far.  I would lean toward a DATA step as well, but using arrays:

 

data want;

set have;

array con {10} contact1-contact10;

array typ {10} type_of_relationship1- type_of_relationship10;

array gen {10} gender1-gender10;

do _n_=1 to 10;

   contact = con{_n_};

   type_of_relationship = typ{_n_};

   gender = gen{_n_};

   output;

end;

keep id contact type_of_relationship gender;

run;

 

You will need more array statements, one for each set of variables in your data.

Contributor K_S
Contributor
Posts: 25

Re: is it possible to "unflatten" files using SAS?

Posted in reply to Astounding

hmmm...it didn't work...

It did flip the dataset, but all the values were missing...

Super User
Posts: 5,503

Re: is it possible to "unflatten" files using SAS?

That's strange, since the technique is very straightforward.  Can you share the log?  It may be a simple thing like changing the spelling of existing variable names.

 

(If possible, it wouldn't hurt to share the contents of a single observation from your incoming data.)

Contributor K_S
Contributor
Posts: 25

Re: is it possible to "unflatten" files using SAS?

Posted in reply to Astounding

there were no error messages in the log. 

Thanks very much for the code though. You gave me enough breadcrumbs to find my way, I think. 

I'll spend some time thinking about it and if I still can't figure it out, I'll let you know Smiley Wink

Trusted Advisor
Posts: 1,560

Re: is it possible to "unflatten" files using SAS?

Posted in reply to Astounding

pay attention - contact and gender are character type.

using array need add $ sign:

 

array con {10} $  contact1-contact10;

array typ {10} type_of_relationship1- type_of_relationship10;

array gen {10} $  gender1-gender10;

Contributor K_S
Contributor
Posts: 25

Re: is it possible to "unflatten" files using SAS?

the macros worked beautifully!!! Thank you!

Ask a Question
Discussion stats
  • 13 replies
  • 218 views
  • 2 likes
  • 4 in conversation