keeping cells with values

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

keeping cells with values

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

IDd1d2d3d4d5d6d7d8
184331723398181951832647531451104941190
200000045540
300000005822
4207082412153161208529638735000
53034925629325881539191751791326212061996
6000189730000
72269360264327312304562371131893676429269
852571616257325041084173128619373300820757
905841000000
1000000060010
1133380253576121342392333262143382569921758
12461440190625310896130341838515501248691382
13467544275333232008171572143652675926178
142723277453633976360000
1500005668402580
         

 

table 2

IDd1d2d3d4d5d6d7d8
184331723398181951832647531451104941190
24554       
35822       
4207082412153161208529638735   
53034925629325881539191751791326212061996
618973       
72269360264327312304562371131893676429269
852571616257325041084173128619373300820757
95841       
106001       
1133380253576121342392333262143382569921758
12461440190625310896130341838515501248691382
13467544275333232008171572143652675926178
14272327745363397636    
1556684258      
         

Accepted Solutions
Solution
‎10-17-2017 04:03 AM
Regular Contributor
Posts: 202

Re: keeping cells with values

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;

View solution in original post


All Replies
Regular Contributor
Posts: 202

Re: keeping cells with values

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.
Contributor
Posts: 40

Re: keeping cells with values

Posted in reply to error_prone

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

Solution
‎10-17-2017 04:03 AM
Regular Contributor
Posts: 202

Re: keeping cells with values

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;
Contributor
Posts: 40

Re: keeping cells with values

Posted in reply to error_prone

Hi error-prone

 

Thanks very much.

 

I followed your codes and it worked perfectly.

 

really appreciate the assistance. 

 

rgds

ejaa

Super User
Super User
Posts: 9,414

Re: keeping cells with values

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).

Contributor
Posts: 40

Re: keeping cells with values

Hi RW9

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

Rgds. ejaa. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 176 views
  • 0 likes
  • 3 in conversation