topic Re: Data step in SAS Programming

Data step

umesh1 — Mon, 03 Apr 2017 16:08:29 GMT

Hi All,

This time I am more descriptive for what I need. I created some of the numbers using the data step and numbers are as follows

data test;

input a b c d;

cards;

10 9 13 10

7 7 9 8

6 7 6 8

2 3 3 4

3 4 6 6

6 7 6 7

8 7 7 7

9 8 11 10

9 9 9 9

;

run;

I would like to acheive below information

Case	a	b	c	d	Flag_a	Flag_b	Flag_c	Flag d	Count	Final_number
1	10	9	13	10	10	9	13	.	3	10
2	7	7	9	8	7	.	9	8	3	8
3	6	7	6	8	6	7	.	8	3	7
4	6	7	6	7	6	7	.	.	4	7
5	9	8	11	10	9	8	11	10	4	9
6	2	3	3	4	2	3	.	4	3	3
7	3	4	6	6	3	4	6	.	4	4
8	8	7	7	7	8	7	.	.	4	8
9	9	9	9	9	9	.	.	.	4	9

Case:1 Any number repeat assgin missing value to the next repeated variable like Flag_d is missing and count column should be 3 because only one missing and in the final_number column I need 10 because minimum of middle two number . e.g 9,10,10,13 middle two number 10 is the minimum numer

Case2: Similar as above example

Case 3:repeated number assign missing value in respective flag variable following the flag_a and count 3 as there are only one missing values in the flag variables and final number is 7

Case 4: there are two missing values in the flag variables such as flag_c and flag_d therefore count is set to 4 and final_number is 7

Case5: there are no repeated numbers means no missing flag count 4 and get the final_number is 3

Re: Data step

ballardw — Mon, 03 Apr 2017 17:00:12 GMT

You appear to be missing part of the requirement.

I think this will assign the flag values though it is a tad difficult to be sure since you appear to have changed the order of the data from the starting data.

data test;
   input a b c d;
   array v a b c d;
   array f flag_a flag_b flag_c flag_d;
   do i= 1 to dim(v);
      if whichn(v[i], of v(*))=i then f[i]=v[i];
   end;
   drop i;
cards;
10 9 13 10
7 7 9 8
6 7 6 8
2 3 3 4
3 4 6 6
6 7 6 7
8 7 7 7
9 8 11 10
9 9 9 9
;
run;

You should not show your results in a different order that the starting example data unless you specifically provide a sort rule.

I cannot tell if the requirement includes adding a case variable or not. Or what the rule would be from the data given.

Your 'count' appears to be inconsistent. What are you counting?

And your "final number" does not make any sense to me at all? You need to provide a much more detailed description.

Re: Data step

umesh1 — Mon, 03 Apr 2017 17:19:15 GMT

Hi ballardw

Thank you so much for your generous help. My point is how many missing values in the flag variables (from flag_a to flag_d). If there is one missing value than count should set to 3 otherwise 4. For the Final rating I need to get the middle number of the flag variables such as middle of (flag_a - flag_d)

I hope this will explain to you my problem

Re: Data step

ballardw — Mon, 03 Apr 2017 17:53:00 GMT

Continuation of https://communities.sas.com/t5/Base-SAS-Programming/Data-step/m-p/346267

@umesh1 wrote:
Hi ballardw

Thank you so much for your generous help. My point is how many missing values in the flag variables (from flag_a to flag_d). If there is one missing value than count should set to 3 otherwise 4. For the Final rating I need to get the middle number of the flag variables such as middle of (flag_a - flag_d)

I hope this will explain to you my problem

6	2	3	3	4	2	3	.	4	3	3
7	3	4	6	6	3	4	6	.	4	4

In the above two rowns you have a "count" of 3 for the first row and a count of 4 for the second. But both are missing exactly one flag value.

And your "middle" definition is not consistent either. On your row 8 with flag_a=8 and flag_b=7 you set 8 as the final but for row 5 you use 9 for the final when there are two values greater. So is your rule a round up or round down or something else.

Re: Data step

Ksharp — Tue, 04 Apr 2017 04:09:36 GMT

data test;
infile cards truncover expandtabs;
input case a b c d;
cards;
1	10	9	13	10	10	9	13	.	3	10
2	7	7	9	8	7	.	9	8	3	8
3	6	7	6	8	6	7	.	8	3	7
4	6	7	6	7	6	7	.	.	4	7
5	9	8	11	10	9	8	11	10	4	9
6	2	3	3	4	2	3	.	4	3	3
7	3	4	6	6	3	4	6	.	4	4
8	8	7	7	7	8	7	.	.	4	8
9	9	9	9	9	9
;
run;
data have;
 set test;
 array x{4} _temporary_;
 array y{4} a b c d;
 array z{4} flag_a flag_b flag_c flag_d;

 call missing(of x{*});
 do i=1 to dim(y);
  if y{i} not in x then do;
   x{i}=y{i};
   z{i}=y{i};
  end;
 end;
 count=ifn(nmiss(of z{*})=1,3,4);
 idx=whichn(min(of x{*}),of x{*});
 if n(of x{*}) ne 1 then x{idx}=.;
 final_number=min(of x{*});
 drop i idx;
run;
proc print;run;

Re: Data step

umesh1 — Thu, 20 Jul 2017 20:33:04 GMT

Hi All,

I have below data set and I need to create the extra columns based to the data set.data set is as follows

data test;

input id a b c d;

cards;

1 . 1 1 2

2 . 1 2 2

3 . 1 5 1

4 . 1 2 4

5 . 1 2 4

6 . 1 1 2

7 4 3 4 2

;

run;

I need to add column count and Want. In the case 1 there are two 1's then want column should have 1

in Case 4 and 5 there is no repeated number that time I need maximum number which is 4. IN case 7 4 is repeated number and maximum number then I need 4 in want column

id	a	b	c	d	count	Want
1	.	1	1	2	3	1
2	.	1	2	2	3	2
3	.	1	5	1	3	1
4	.	1	2	4	3	4
5	.	1	2	4	3	4
6	.	1	1	2	3	1
7	4	3	4	2	4	4

Let me know if some one needs more clarifcation, please.

Thanks in advance

Re: Data step

Ksharp — Fri, 21 Jul 2017 10:45:52 GMT

Please bring a brand new session to let more sas user to see it.

I think the most easy way is using IML code.

data test;
input id a b c d;
cards;
1 . 1 1 2
2 . 1 2 2
3 . 1 5 1
4 . 1 2 4
5 . 1 2 4
6 . 1 1 2
7 4 3 4 2
;
run;
proc iml;
use test nobs nobs;
read all var{a b c d} into x[c=vname];
close;

want=j(nobs,1,.);
do i=1 to nobs;
call tabulate(level,freq,x[i,]);
idx=loc(freq=max(freq));
want[i]=max(level[idx]);
end;

create w var{want};
append ;
close;
quit;

data want;
 merge test w;
run;

Re: Data step

umesh1 — Fri, 21 Jul 2017 13:47:39 GMT

Hi,

Somehow I do not have the proc IML access due to the limited access. Could you help me to write the code in the data step or other way, please?

Thanks,

Re: Data step

Ksharp — Sat, 22 Jul 2017 13:44:51 GMT

You can download the SAS University Edition from sas website. It is totally free and you can use IML.

Re: Data step

Ksharp — Sat, 22 Jul 2017 14:50:54 GMT

OK. Here is .

data test;
input id a b c d;
count=n(a,b,c,d);
cards;
1 . 1 1 2
2 . 1 2 2
3 . 1 5 1
4 . 1 2 4
5 . 1 2 4
6 . 1 1 2
7 4 3 4 2
;
run;

data temp;
 set test;
 array x{*} a b c d;
 do i=1  to dim(x);
   if not missing(x{i}) then do;value=x{i};output;end;
 end;
 keep id value;
run;
proc freq data=temp noprint ;
table id*value/out=temp1 list;
run;
proc sort data=temp1;
by id count value;
run;
data temp2;
 set temp1;
 by id;
 if last.id;
run;

data want;
 merge test temp2(rename=(value=want) keep=id value);
 by id;
run;