topic Re: How to create a table containing the first number only in New SAS User

How to create a table containing the first number only

hjjijkkl — Fri, 29 Jan 2021 23:55:13 GMT

I have a table below and I want to create a table where the first 'ID' is 'test'' =1. If the first 'ID' isn't 'test' =1 then to have it in a separate column.

ID	date	test
1	6/7/18	1
1	8/12/28	1
2	5/4/18	3
3	6/20/18	1
3	10/4/18	2
4	5/8/18	3
4	7/23/18	3
5	3/9/18	1
5	7/4/18	1
5	11/20/18	1

I want the output to look like this

ID	date	test	Test1	Test2
1	6/7/18	1	1
1	8/12/28	1
2	5/4/18	2		2
3	6/20/18	1	1
3	10/4/18	2
4	5/8/18	3		3
4	7/23/18	3
5	3/9/18	1	1

Re: How to create a table containing the first number only

Angel_Larrion — Sat, 30 Jan 2021 02:45:18 GMT

The following code does something similar, if it is not what you need please specify a little more

data want;
set have;
by id;
if first.id then do;
if test=1 then test1=1;
else if test ne 1 then test2=test; 
end;
run;

Re: How to create a table containing the first number only

mkeintz — Sat, 30 Jan 2021 04:11:07 GMT

Questions:

Why does the 3rd line of your top table have test=3, but the corresponding line in the bottom table have test=2?
Why does the top table have 3 lines for id=5, but the bottom table have only one such line?

Assuming Q1 is just a typographical error, and the missing lines for ID=5 are just an oversight, this might be a good program to implement a simple 2-element array:

data want;
  set have;
  by id;
  array tst {2} test1 test2;
  if first.id then tst{2-(test=1)}=test;
run;

The "trick" here is the evaluation of the expression "(test=1)" which yields either a 1 (if true) or 0 (otherwise).

So, at the start of an id, TEST=1, then the result is tst{2-1}=test, i.e. tst{1}=test). The array reference tst{1} is variable test1, so it's test1=test.

But if the TEST is anything other than a one, then the result is tst{2-0}=test, i.e. tst{2}=test, meaning test2=test.

Re: How to create a table containing the first number only

Kurt_Bremser — Sat, 30 Jan 2021 09:11:08 GMT

Slightly different take:

data want;
set have;
by id;
if first.id
then if test = 1
  then test1 = 1;
  else test2 = test; 
run;