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

Hai

  I have a doubt in data set programming

Here i can show my dataset.

IDTest1_Test2_Test3_test
U1Ta
U1Tb

U1Tc
U2Bi
U2Bj
U3Ck
U3Cl
U3Cm
U3Cn

I would like to become the Varible 4_Test as shown in below

ID4_test
U1T
U1a
U1b
U1c
U2B
U2i
U3j
U3C
U3k
U3l
U3m
U3n

Please Help me :smileyplain:

Regards

Krishna

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

This is tested based on the data you provided. Mistakes in original code include a bug in array declaration (character and missing brace) and extra output statement.

data have;

infile datalines missover;

length ID Test Test1-Test3 $8;

input ID Test Test1-test3;

datalines;

U1 T a

U1 T b

U1 T c

U2 B . i

U2 B . j

U3 C . . k

U3 C . . l

U3 C . . m

U3 C . . n

;

data want;

set have;

by ID ;

array t_(3) $ test1-test3;

test4=test;

if first.id then output;

do i=1 to 3;

test4=t_(i);

if not missing(test4) then output;

end;

keep ID test4;

run;

View solution in original post

11 REPLIES 11
Reeza
Super User

Have you tried a transpose?

Otherwise a data step is a good way, using an output statement to control the output.

data want;

set have;

array t_3) test1-test3;

test4=test;

output;

do i=1 to 3;

test4=t_(i);

output;

end;

keep ID test4;

run;

PGStats
Opal | Level 21

You will have to use variable names starting with alphabetic characters (1_test is not a valid name, change it to test_1), then expand your dataset with:

data myDataset;

infile datalines missover;

length ID Test Test_1-Test_3 $8;

input ID Test Test_1 Test_2 Test_3;

datalines;

U1 T a

U1 T b

U1 T c

U2 B . i

U2 B . j

U3 C . . k

U3 C . . l

U3 C . . m

U3 C . . n

;

data want;

set myDataset; by test notsorted;

array t Test_:;

if first.test then output;

do i = 1 to dim(t);

    test = t{i};

    if not missing(test) then output;

    end;

keep ID test;

run;

proc print data=want noobs; run;

PG

PG
KrishnaChandra
Calcite | Level 5

Hai PG,

   Thanx.. But the test value T,B,C are  repeats ,then how it possible to out the observation(first.Test)???

PGStats
Opal | Level 21

Try replacing

set myDataset; by test notsorted;


by

set myDataset; by ID test notsorted;


That will output one observation for each consecutive ID with the same test.

PG

PG
PoornimaRavishankar
Quartz | Level 8

Also not missing(test) has to change since they are character values. try if trim(left(compress(test))) ne ''

PGStats
Opal | Level 21

The MISSING function works both for numeric and character arguments. The function returns true for empty strings. - PG

PG
KrishnaChandra
Calcite | Level 5

but first.test then output;  The code doesn't work properly.The test value not shown .i meant(T B C)


Reeza
Super User

This is tested based on the data you provided. Mistakes in original code include a bug in array declaration (character and missing brace) and extra output statement.

data have;

infile datalines missover;

length ID Test Test1-Test3 $8;

input ID Test Test1-test3;

datalines;

U1 T a

U1 T b

U1 T c

U2 B . i

U2 B . j

U3 C . . k

U3 C . . l

U3 C . . m

U3 C . . n

;

data want;

set have;

by ID ;

array t_(3) $ test1-test3;

test4=test;

if first.id then output;

do i=1 to 3;

test4=t_(i);

if not missing(test4) then output;

end;

keep ID test4;

run;

KrishnaChandra
Calcite | Level 5

Thanx Reeza Smiley Happy

Reeza
Super User

data want;

set have;

by ID ;

array t_3) test1-test3;

test4=test;

if first.id then output;

do i=1 to 3;

test4=t_(i);

if not missing(test4) then output;

output;

end;

keep ID test4;

run;

KrishnaChandra
Calcite | Level 5

Thanx PG Smiley Happy

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
  • 11 replies
  • 1048 views
  • 4 likes
  • 4 in conversation