BookmarkSubscribeRSS Feed
K_S
Obsidian | Level 7 K_S
Obsidian | Level 7

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? 

13 REPLIES 13
LinusH
Tourmaline | Level 20
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
K_S
Obsidian | Level 7 K_S
Obsidian | Level 7

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

 

 

 

Astounding
PROC Star

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

K_S
Obsidian | Level 7 K_S
Obsidian | Level 7

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 🙂

Astounding
PROC Star

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.

K_S
Obsidian | Level 7 K_S
Obsidian | Level 7

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?

 

Shmuel
Garnet | Level 18

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 */
Astounding
PROC Star

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.

K_S
Obsidian | Level 7 K_S
Obsidian | Level 7

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

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

Astounding
PROC Star

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

K_S
Obsidian | Level 7 K_S
Obsidian | Level 7

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 😉

Shmuel
Garnet | Level 18

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;

K_S
Obsidian | Level 7 K_S
Obsidian | Level 7

the macros worked beautifully!!! Thank you!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1162 views
  • 2 likes
  • 4 in conversation