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

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   .
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

--
Paige Miller

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
RandoDando
Pyrite | Level 9

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 

 

RandoDando
Pyrite | Level 9

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
Tom
Super User Tom
Super User

@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

 

ballardw
Super User

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1043 views
  • 0 likes
  • 4 in conversation