BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EJAA
Obsidian | Level 7

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      
         
1 ACCEPTED SOLUTION

Accepted Solutions
error_prone
Barite | Level 11

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

6 REPLIES 6
error_prone
Barite | Level 11
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.
EJAA
Obsidian | Level 7

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

error_prone
Barite | Level 11

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;
EJAA
Obsidian | Level 7

Hi error-prone

 

Thanks very much.

 

I followed your codes and it worked perfectly.

 

really appreciate the assistance. 

 

rgds

ejaa

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

EJAA
Obsidian | Level 7

Hi RW9

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

Rgds. ejaa. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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