topic keeping cells with values in SAS Programming

keeping cells with values

EJAA — Tue, 17 Oct 2017 06:07:40 GMT

Hi,

I have a large number of columns (1000s) and rows as shown in table 1 with some cells having no observation.

I want to create a new table where only cells with values will remain starting from column (fdi1) as shown in table 2 for easy cross checking etc. I tried the following but it didn't seem to work as a newbie. how do i resolve this problem. thanks in advance for the help. rgds.

data want;
set have;
by ID;
if d1-d8 ^=.0 then keep;
else remove;
run;

TABLE 1

ID	d1	d2	d3	d4	d5	d6	d7	d8
1	8433	1723398	18195	18326	4753	1451	10494	1190
2	0	0	0	0	0	0	4554	0
3	0	0	0	0	0	0	0	5822
4	2070824	12153	16120	8529	638735	0	0	0
5	30349	25629	32588	1539	19175	17913	2621	2061996
6	0	0	0	18973	0	0	0	0
7	22693	602643	27312	30456	23711	31893	6764	29269
8	5257	1616257	32504	10841	731286	1937	33008	20757
9	0	5841	0	0	0	0	0	0
10	0	0	0	0	0	0	6001	0
11	33380	253576	12134	23923	33262	14338	25699	21758
12	461440	1906253	10896	13034	18385	15501	2486	91382
13	467544	275	33323	20081	71572	14365	26759	26178
14	272327	7453	6339	7636	0	0	0	0
15	0	0	0	0	56684	0	258	0

table 2

ID	d1	d2	d3	d4	d5	d6	d7	d8
1	8433	1723398	18195	18326	4753	1451	10494	1190
2	4554
3	5822
4	2070824	12153	16120	8529	638735
5	30349	25629	32588	1539	19175	17913	2621	2061996
6	18973
7	22693	602643	27312	30456	23711	31893	6764	29269
8	5257	1616257	32504	10841	731286	1937	33008	20757
9	5841
10	6001
11	33380	253576	12134	23923	33262	14338	25699	21758
12	461440	1906253	10896	13034	18385	15501	2486	91382
13	467544	275	33323	20081	71572	14365	26759	26178
14	272327	7453	6339	7636
15	56684	258

Re: keeping cells with values

error_prone — Tue, 17 Oct 2017 06:34:57 GMT

Description and table2 don't match.

It seems that you want to move values to the left, replacing zeroes with the values available, remaining zeroes will be replaced by missing. Right?

Could be done using a loop in a datastep. Depending on the next use of the data transposing could be an interesting option.

Re: keeping cells with values

EJAA — Tue, 17 Oct 2017 06:39:29 GMT

Yes i want to move values to the left to replace zero when values are available just as you have said. For instance some firms have values in column 900 and want to move it to the first column. any help for me?

Thanks

Re: keeping cells with values

error_prone — Tue, 17 Oct 2017 07:30:52 GMT

Using proc transpose twice:

data have;

   input ID d1 d2 d3 d4 d5 d6 d7 d8;

datalines;
1 8433 1723398 18195 18326 4753 1451 10494 1190
2 0 0 0 0 0 0 4554 0
3 0 0 0 0 0 0 0 5822
4 2070824 12153 16120 8529 638735 0 0 0
5 30349 25629 32588 1539 19175 17913 2621 2061996
6 0 0 0 18973 0 0 0 0
7 22693 602643 27312 30456 23711 31893 6764 29269
8 5257 1616257 32504 10841 731286 1937 33008 20757
9 0 5841 0 0 0 0 0 0
10 0 0 0 0 0 0 6001 0
11 33380 253576 12134 23923 33262 14338 25699 21758
12 461440 1906253 10896 13034 18385 15501 2486 91382
13 467544 275 33323 20081 71572 14365 26759 26178
14 272327 7453 6339 7636 0 0 0 0
15 0 0 0 0 56684 0 258 0
;
run;

proc transpose data=have 
      prefix=value
      out=transposed(rename=(value1=value) where=(value ne 0))
   ;
   by Id;
   var d1-d8;
run;

proc transpose data=transposed
      prefix=d
      out=want(drop=_name_)
   ;
   by Id;
   var value;
run;

Re: keeping cells with values

RW9 — Tue, 17 Oct 2017 07:57:18 GMT

For future reference Post your test data in the form of a datastep and use the {i} code window to retain formatting. As such I don't have time to type all that in so this code is untested:

data want;
  set have;
  array d{8};
  do i=8 to 2 by -1;
    if d{i-1}=0 then do;
      d{i-1}=d{i};
      d{i}=.;
    end;
  end;
run;

This should loop backwards through your variables and move values back one, less processing than the proc transpose, but depending on your data might not work (i.e. multiple gaps would cause and issue).

Re: keeping cells with values

EJAA — Tue, 17 Oct 2017 08:03:01 GMT

Hi error-prone

Thanks very much.

I followed your codes and it worked perfectly.

really appreciate the assistance.

rgds

ejaa

Re: keeping cells with values

EJAA — Tue, 17 Oct 2017 08:07:07 GMT

Hi RW9

Thanks for the direction in posting questions. Will do that next time.

Rgds. ejaa.