concatenate 2 character vars where there are multiple choices for each

[urban58]

Hello SAS community,

I successfully imported an excel sheet with the following columns, I have been asked to concatenate visitvarname (hundreds of these combos).

visit varname
V0, V7 AGE
V0, V7 SEX
V0, V7 RACE
V0, V7 HISP
V0, V1, V2, V3, V5, V7, V8, V9 ZLKL/ZRKL
V0, V1, V2, V3, V5, V7, V8, V9 ZROSFL/ZLOSFL
V0, V1, V2, V3, V5, V7, V8, V9 ZROSFM/ZLOSFM
V0, V1, V2, V3, V5, V7, V8, V9 ZROSTL/ZLOSTL
V00, V01, V02, V03, V04, V05, V06, V07, V08, V09, V10, V11 WOOTSR, WOOTSL
V00, V01, V02, V03, V04, V05, V06, V07, V08, V09, V10, V11 UPR, UPL; DOWNR, DOWNL

I'm hoping I can get some help to automate the process.

WANT visitvarname,

V0, V7 AGE becomes V0AGE, V7AGE

V0, V1, V2, V3, V5, V7, V8, V9 ZLKL/ZRKL becomes
V0ZLKL, V1ZLKL, V2ZLKL, V3ZLKL, V5ZLKL, V7ZLKL, V8ZLKL, V9ZLKL/V0ZRKL, V1ZRKL, V2ZRKL, V3ZRKL, V5ZRKL, V7ZRKL, V8ZRKL, V9ZRKL

V00, V01, V02, V03, V04, V05, V06, V07, V08, V09, V10, V11 WOOTSR, WOOTSL becomes
V00WOOTSR, V01WOOTSR, ... V11WOOTSR/V00WOOTSL, V01WOOTSL, ... V11WOOTSL

etc.

I have imported the SAS dataset but am stumped how to do the concatenation.
It seems that loops and functions other than coalescec might be needed.
Transposing the data into long form would seem an option but then how would I get each visitvarname combo transposed back into a single row?

data want;
set have;
visits_varname = coalescec (visits, varname);
run;

Appreciate any help!
Maggie

[Tom]

COALESCEC() and CATS() are two different operations.  They might have similar results when only one of two variables has a non-missing value.  But otherwise they are different.

Can you explain WHY you want to do this?

Can you show a simple example input and the desired output for that input?

[urban58]

Hi Tom,

Sorry for the poor quality of my question

1. The data from the excel I shared is part of a technical document which has broken down actual SAS variable names into visits and the varname, which is a suffix - vist + suffix together make up the actual variable names in the SAS datasets.
I have been asked to put them back together. There are 100s of rows of these multiple visits and multiple suffixes to put back together.

2. There is no simple example input, the sample data is the output from what's in the excel sheet (technical doc) 

The choices within the visit variable are V0, V1, V2, V3, etc. - number of visits vary depending on varname.

V0=Visit 0
V1=Visit 1
etc.

The choices within the varname or suffix can be something as simple as AGE, or more complex like ZLKL/ZRKL. For ZLKL/ZRKL, each visit choice (V0, V1, etc.) needs to be concatenated first to ZLKL, when that's done then each visit choice (V0, V1, etc.) needs to be concatenated to ZRKL.

The delimiter in the suffix (varname column) can be a forward slash, a comma or a semicolon

what I WANT is 

visit                                                  varname                sas_varname
V0, V7                                             AGE                        V0AGE,V7AGE
V0, V1, V2, V3, V5, V7, V8, V9 ZROSFL/ZLOSFL          V0ZLKL,V1ZLKL,V2ZLKL,etc./V0ZRKL,V1ZRKL,V2ZRKL,etc.
V0, V1, V2, V3, V4, V5, V6, V7, V8, V9, V10, V11 UPR, UPL; DOWNR, DOWNL V0UPR,V1UPR,V2UPR,etc./V0UPL,V1UPL,V2UPL,etc./V0DOWNR,V1DOWNR,V2DOWNR,etc./V0DOWNL,V1DOWNL,V2DOWNL,etc.

Thanks for your help.
Maggie

[Tom · Accepted solution]

So it sounds like you have TWO input variables, either or which could contain a delimited list of short strings.  And your goal is to make all possible combinations.

So let's assume your HAVE dataset looks like:

data have;
  infile datalines dsd dlm='|' truncover;
  input visit :$200. varname :$200.;
datalines;
V0, V7 | AGE
V0, V1, V2, V3, V5, V7, V8, V9 | ZROSFL/ZLOSFL V0ZLKL,V1ZLKL,V2ZLKL,/V0ZRKL,V1ZRKL,V2ZRKL
;

Then just scan over both lists and concatenate them.

data want;
  length sas_varname visitpart varpart $32.;
  set have;
  do i=1 to countw(visit);
    do j=1 to countw(varname);
      visitpart=scan(visit,i);
      varpart=scan(varname,j);
      sas_varname=cats(visitpart,varpart);
      output;
    end;
  end;
run;

[urban58]

Hi Tom,

That's fantastic, thank you!

I really appreciate your excellent help.

Maggie

[ballardw]

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

My first impression of this is that someone is going to take a bunch of ugly data sets imported from Excel and continue on to make even uglier sets using this to possibly rename previously created data set variables.

Rules are important. What is the exact rule to use for making these values? You are making us guess what the rule is for using a value like ZLKL/ZRKL much less that horrible V11 WOOTSR, WOOTSL . Guessing in general is a poor approach to programming.

If this is involving other data sets perhaps those values you are appending into the names should be ADDED to the data as a new variable to describe the values in the other observations instead of placing information into variable names.

Note, that for many programming tasks in SAS it would be much easier to use a list of variables named AGEV0 Agev7 because you could use a list like AgeV: to get all of the variables. Your proposed values would eliminate the chance of using shortcut lists.