topic Re: Data managing in SAS Programming

Data managing

sijansap — Wed, 03 May 2017 15:39:01 GMT

Dear SAS community,

I got the following problem on data manipulation. I would appreciate very much for you kind help.

Thank you.

data HAVE;

input X1 X2 X3 X4;

DX1-X20=Substr(X1-X20, 1, 3); /*All input values are charecters, and I have over 300k rows with 20

columns, there are no data after certain column in each row*/

cards;

360 361 290 3321

360

400 4021 405

225

112 227 865 342

360 443 361

360 870

360

run;

I WANT:

if 360 is in first column, and there are no other input in any other column in the same row then count is one otherwise zero. So in the above data, for 360 I want count=1 from second row and count=1 each from last two rows rest zeros. Similarly for 225 I should get count=1 only from 4th row, etc.

I would appreciate very for your kindly help.

Re: Data managing

PeterClemmensen — Wed, 03 May 2017 17:12:25 GMT

You say that X1-X4 are character variables, but you have defined them as numeric?

I think you want something like this:

data have;
input X1 $ X2 $ X3 $ X4 $;
infile cards missover;
cards;
360 361 290 3321
360
400 4021 405 
225
112 227 865 342
360 443 361
360 870
360
360
run;

data want;
 set have;
 if cmiss(X2,X3,X4)=3 then count = 1;
 else count = 0;
run;

Re: Data managing

mkeintz — Wed, 03 May 2017 18:08:28 GMT

YOu want the count of non-blank character variables. But your data sample is numeric. In that case, you can use the N function (number of non-missing values). The "of x:" argument implies a list of all variables whose name begins with x.

data HAVE;
infile datalines missover;
input X1 X2 X3 X4; 
cards;
360  361     290  3321
360
400  4021   405 
225
112  227    865          342
360            443   361
360            870
360
360
run;

data want;
  set have;
  nx=n(of x:);
  put _n_= nx=;
run;

Now if the vars really are character, then you can concatenate them with comma separation (catx function), and then count the number of comma-separated "words" (countw) in the concatenation:

data HAVE;
infile datalines missover;
input (X1 X2 X3 X4) (:$4.);
cards;
360  361     290  3321
360
400  4021   405 
225
112  227    865          342
360            443   361
360            870
360
360
run;

data want;
  set have;
  nx=countw(catx(',',of x:));
run;

Re: Data managing

sijansap — Wed, 03 May 2017 18:20:00 GMT

Thank you so much dear Draycut.

I used

& missing(X2) & missing(X3) & .... I worked perfectly.

Thanks again and I appreciate your help very much.

Re: Data managing

PeterClemmensen — Wed, 03 May 2017 20:38:08 GMT

Anytime, glad to help 🙂

Re: Data managing

ballardw — Wed, 03 May 2017 22:33:39 GMT

Please note that where/how you paste code in this forum makes a difference.For example pasting into the main message window as you did resulted in this:

Data HAVE;

input X1 X2 X3 X4;

cards;

360 361 290 3321

360

400 4021 405

225

112 227 865 342

360 443 361

360 870

;

But the SAME source from the SAS editor pasted into a code box using the forum {i} menu icon shows:

data HAVE;
input X1 X2 X3 X4; 
cards;
360  361     290  3321
     360
400  4021   405 
225
112  227    865   342
360         443   361
360         870
;

Why do I mention this? Your request mentions "when 360 is in the first column". The code in the code box for the second row of data would tend to imply the 360 is in the second "column" and the other paste would have it in the first.

Also your data step as pasted may well have issues other than the numeric / character confusion pointed out. Reading the last two rows will have 443 and 870 in the x2 variable. Was that the intent?

Re: Data managing

sijansap — Wed, 03 May 2017 23:22:58 GMT

Thank you for your time BallardW. I used the codes suggested by Darycut and it worked for me.

ThanKs anyway.