How to: move data from columns to the left

[arde]

Hi,

Would anyone know how to move data in columns to the left so that there's no missing cells in between them:

have:

Title	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9
Colors1	Red	Blue	Black		Yellow	Green
Colors2	Red			Purple	Yellow			Orange	Violet
Colors3	Red		Black		Yellow		White	Orange	Violet
Colors4	Red			Purple	Yellow			Orange	Violet

want:

Title	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9
Colors1	Red	Blue	Black	Yellow	Green
Colors2	Red	Purple	Yellow	Orange	Violet
Colors3	Red	Black	Yellow	White	Orange	Violet
Colors4	Red	Purple	Yellow	Orange	Violet

thank you

[PaigeMiller]

May I ask what you plan to do with this data? What is the next analysis or report that will be done once you remove these missing cells?

What is it you can do without the missing cells that you can't do with the missing cells present?

Please explain.

[arde]

I am creating a csv file.  the csv file is being used to create a display.  I have 10,000 rows.  The display doesn't look so nice because of all the gaps in between the data.

[Kurt_Bremser]

Take the opportunity and convert your dataset to a usable long structure.

proc transpose data=have out=want (where=(col1 ne ""));
by title;
var column:;
run;

[arde]

will proc transpose work?  I have 10,000 rows in my main file

[SASKiwi]

There is no harm at all in just trying it as an experiment. Even if it fails it will likely move you closer to a solution.

[Patrick · Accepted solution]

Below one way how you could do this.

data have;
  infile datalines truncover dsd;
  input (Title Column1-Column9) ($);
  datalines;
Colors1,Red,Blue,Black,,Yellow,Green,,,
Colors2,Red,,,Purple,Yellow,,,Orange,Violet
Colors3,Red,,Black,,Yellow,,White,Orange,Violet
Colors4,Red,,,Purple,Yellow,,,Orange,Violet
;

data want;
  set have;
  array _cols {*} Column9 Column8 Column7 Column6 Column5 Column4 Column3 Column2 Column1 ;
  call sortc(of _cols[*]);
run;

[arde]

thank you so much

[Ksharp]

data have;
  infile datalines truncover dsd;
  input (Title Column1-Column9) ($);
  datalines;
Colors1,Red,Blue,Black,,Yellow,Green,,,
Colors2,Red,,,Purple,Yellow,,,Orange,Violet
Colors3,Red,,Black,,Yellow,,White,Orange,Violet
Colors4,Red,,,Purple,Yellow,,,Orange,Violet
;

data want;
  set have(rename=(column1-column9=_c1-_c9));
  array _cols {*} $ _c1-_c9 ;
  array x {*} $ 80 column1-column9 ;
  n=0;
  do i=1 to dim(_cols);
    if not missing(_cols{i}) then do;n+1;x{n}=_cols{i};end;
  end;
  keep Title Column1-Column9;
run;