topic Re: How do I insert a new record after each record with conditions? in SAS Programming

How do I insert a new record after each record with conditions?

RandoDando — Wed, 24 Apr 2024 17:50:06 GMT

I want to insert new rows below each record, where the text in one column (in my example Text2) is not null. If it is null, then no inserting of a row. In the inserted row, I want to copy the values of the first 2 grouping variables from the record, insert the text of "Text2" in the field of "Text1", and set the last 2 columns as null and . (null numeric) respectively.

Sorry for the dummy data, but here is what I have:

Group	Class	Text1	Text2	other1	count
1	A	Text for Record 1	text1	AAAA	1
1	A	Text for Record 2		CCCC	2
1	B	Text for Record 3	text3	EEEE	3
1	C	Text for Record 4	text4	GGGG	4

Here is what I want (inserted records in bold):

Group	Class	Text1	other1	count
1	A	Text for Record 1	AAAA	1
1	A	text1		.
1	A	Text for Record 2	CCCC	2
1	B	Text for Record 3	EEEE	3
1	B	text3		.
1	C	Text for Record 4	GGGG	4
1	C	text4		.

Re: How do I insert a new record after each record with conditions?

PaigeMiller — Wed, 24 Apr 2024 18:28:32 GMT

UNTESTED CODE

data want;
    set have;
    output;
     if not missing(text2) then do;
         text1=text2;
         other=' ';
         count=.;
         output;
    end;
run;

If you want tested code, please from now on provide the data as working SAS data step code (examples and instructions).

Re: How do I insert a new record after each record with conditions?

RandoDando — Wed, 24 Apr 2024 19:03:33 GMT

That did it! Thanks!

Re: How do I insert a new record after each record with conditions?

RandoDando — Fri, 26 Apr 2024 13:48:07 GMT

While that worked, I've hit a snag. I decided to set a text variable in the data set equal to another text variable for the inserted rows. Text3 is the original column I am inserting the text of Text4 into for the inserted rows. However, since the text for Text4 is longer for some cases it's getting truncated despite my efforts to set the length of each to something which can accommodate both. It seems that every character in Text4 beyond the max length used in Text3 isn't showing, despite the length being high enough.

data want;
    set have;
    output;
     if not missing(text2) then do;
         text1=text2;
         text3=text4;
         other=' ';
         count=.;
         output;
    end;
run;4

Re: How do I insert a new record after each record with conditions?

RandoDando — Fri, 26 Apr 2024 13:56:23 GMT

Figured out a trick. Instead of using the Length=x statement in PROC SQL I just used a case statement with a nonsense condition.

Case when Text3 = "XYZ" then "                                    " else Text3 end as Text3

Re: How do I insert a new record after each record with conditions?

Tom — Fri, 26 Apr 2024 14:03:35 GMT

@RandoDando wrote:

Figured out a trick. Instead of using the Length=x statement in PROC SQL I just used a case statement with a nonsense condition.
Case when Text3 = "XYZ" then "                                    " else Text3 end as Text3

That does not look like a nonsense condition. If by that you mean a condition that is always false you would use 1=0 or in SAS logic just 0 since SAS will treat the number zero as FALSE.

And for your stated example of inserting values of TEXT4 you could just reference to it directly to make sure the new variable has proper length.

case when 1=0 then ' ' else text4 as text3

Re: How do I insert a new record after each record with conditions?

ballardw — Fri, 26 Apr 2024 14:43:11 GMT

I might suggest something like:

data want ;
    set have (rename=(text3=oldtext));
    /* this will create new text3 variable the length of text4*/
    text3= text4;
    /* set new variable to values of Text3 coming in for your output*/
    text3=oldtext ;
    output;
     if not missing(text2) then do;
         text1=text2;
         text3=text4;
         other=' ';
         count=.;
         output;
    end;
    drop oldtext;
run;

If you are combining text from the old Test3 and Text4 then use Text3= oldtext || text4; to create variable the length of text3 and text4 combined to hold more text.

You don't show any attempt to change the length of Text3 so I have to assume it was after the SET statement. Once the variable is in the data vector you can't change the length or the type. If you expect to read a variable from data set on the SET statement and need to change the length you need to do so before the Set:

This will set the length of the variable Somevar to 15 regardless of the length in the data set Old.

data new;
     length somevar $ 15;
    set old;
run;

The log will warn you if you specify a length shorter than existing that data may be truncated.