Restructure messy data

[JAR]

Hi All,

I need some help with the following:

This data is saved from some traditional source (Ms Word). Though one can do this easily in excel, I wonder how to do this in SAS. 

Have			Want
Category	Item		Item	Category
Dog			Sheepdogs	Dog
	Sheepdogs		Pinschers	Dog
	Pinschers		Terriers	Dog
	Terriers		Dachshunds	Dog
	Dachshunds		Abyssinian	Cat
Cat			Aegean	Cat
	Abyssinian		Balinese	Cat
	Aegean		Bambino	Cat
	Balinese		Bengal	Cat
	Bambino		Birman	Cat
	Bengal
	Birman

Thanks, 
JAR

[PeterClemmensen · Accepted solution]

You can do like this

data have;
input Category $ 1-5 Item $ 7-20;
datalines;
Dog                 
      Sheepdogs     
      Sheepdogs     
      Pinschers     
      Terriers      
      Dachshunds    
Cat                 
      Abyssinian    
      Aegean        
      Balinese      
      Bambino       
      Bengal        
      Birman        
;

data want(drop=_:);
   set have;
   retain _;
   if not missing(Category) then _=Category;
   if not missing(Item);
   Category=_;
run;

[JAR]

Thank you!

[PeterClemmensen]

Anytime, glad to help 🙂