topic Restructure messy data in New SAS User

Restructure messy data

JAR — Sun, 02 Jun 2019 11:56:05 GMT

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

Re: Restructure messy data

PeterClemmensen — Sun, 02 Jun 2019 14:30:46 GMT

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;

Re: Restructure messy data

JAR — Sun, 02 Jun 2019 17:08:52 GMT

Thank you!

Re: Restructure messy data

PeterClemmensen — Sun, 02 Jun 2019 20:02:08 GMT

Anytime, glad to help 🙂