topic Re: After missing new column should start with sequence in SAS Programming

After missing new column should start with sequence

sivastat08 — Mon, 25 Sep 2017 10:55:51 GMT

i want to create new column whereever missing is after that it should start give number like sequence123.123.123.

below is my data ihave tried but i not getting the exact one.please help me,

data new;
input have ;
datalines;

2
1
9
.
1
2
.
5
1
2
.
9
7
5
;

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_NEW AS
SELECT t1.have,
/* getting*/
(case when t1.have = . then . else monotonic() end) AS getting
FROM WORK.NEW t1;
QUIT;

have	getting	Want
.	.
2	1	1
1	2	2
9	3	3
.	.
1	4	1
2	5	2
.	.
5	6	1
1	7	2
2	8	3
.	.
9	9	1
7	10	2
5	11	3

Re: After missing new column should start with sequence

Jim_G — Mon, 25 Sep 2017 11:07:16 GMT

Try adding this code to your first data step.

retain want 0;

if have=. then want=.;

else; want+1;

Jim

Re: After missing new column should start with sequence

sivastat08 — Mon, 25 Sep 2017 11:17:28 GMT

Hi Sir ,thanks for the quick answer.

Re: After missing new column should start with sequence

Jim_G — Mon, 25 Sep 2017 11:20:41 GMT

data new;
retain want 0;

input have;
if have=. then do; want=.; end;
else want+1;

datalines;

2
1
9
.
1
2
.
5
1
2
.
9
7
5
;

proc print; run;

Re: After missing new column should start with sequence

sivastat08 — Mon, 25 Sep 2017 11:22:51 GMT

i tried above code

Data New1;
set new;
retain want 0;
if have=. then want=.;
want+1;
run;

i got wherever missing(.) its retained as 1 so tried instead of missing(.) as (-1) its working fine.

Data New1;
set new;
retain want 0;
if have=. then want= -1;
want+1;
run;

Re: After missing new column should start with sequence

sivastat08 — Mon, 25 Sep 2017 11:26:03 GMT

thanks for the answer sir. this code is working. thank you sir.