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 | . |
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).
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).
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
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
@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
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 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.