topic Re: How to: move data from columns to the left in SAS Programming

How to: move data from columns to the left

arde — Sat, 13 Aug 2022 16:48:19 GMT

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

Re: How to: move data from columns to the left

PaigeMiller — Sat, 13 Aug 2022 17:08:14 GMT

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.

Re: How to: move data from columns to the left

Kurt_Bremser — Sat, 13 Aug 2022 17:40:32 GMT

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;

Re: How to: move data from columns to the left

arde — Sat, 13 Aug 2022 22:04:38 GMT

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

Re: How to: move data from columns to the left

arde — Sat, 13 Aug 2022 22:06:06 GMT

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.

Re: How to: move data from columns to the left

SASKiwi — Sat, 13 Aug 2022 22:23:52 GMT

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.

Re: How to: move data from columns to the left

Patrick — Sat, 13 Aug 2022 22:40:44 GMT

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;

Re: How to: move data from columns to the left

arde — Sun, 14 Aug 2022 00:46:33 GMT

thank you so much

Re: How to: move data from columns to the left

Ksharp — Sun, 14 Aug 2022 09:49:30 GMT

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;