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

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

6 REPLIES 6
PeterClemmensen
Tourmaline | Level 20

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;

sijansap
Obsidian | Level 7

Thank you so much dear Draycut.

I used

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

Thanks again and I appreciate your help very much.

PeterClemmensen
Tourmaline | Level 20

Anytime, glad to help 🙂

mkeintz
PROC Star

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;

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ballardw
Super User

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?

sijansap
Obsidian | Level 7

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

ThanKs anyway.

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
  • 904 views
  • 1 like
  • 4 in conversation