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

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

 

1 ACCEPTED SOLUTION
3 REPLIES 3
Angel_Larrion
SAS Employee

 

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;
mkeintz
PROC Star

Questions:

  1. Why does the 3rd line of your top table have test=3, but the corresponding line in the bottom table have test=2?
  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.

 

 

 

 

--------------------------
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

--------------------------

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 519 views
  • 1 like
  • 4 in conversation