concatenate several NUM vars to 1 string

[oggesjolin]

Hi!

I need to concatenate from 1 to 20 numeric variables into one string, each value separated with a comma, but I only want to concatenate when I do have a legitimate value in the numeric var. For instance:

V1 101
V2 155
V3 322
V4 $sysmis
V5 102

..should become a string variable with "101,155,322,102" not "101,155,322,,102".

Could someone help?

[Andy W]

You can use DO REPEAT to loop over the variables and sucessively concatenate values. Example below:

*********************************.
*Making fake data.
DATA LIST FREE / V1 TO V5.
BEGIN DATA
101 155 322 . 102
END DATA.

STRING NumC (A100).
DO REPEAT V = V1 TO V5.
  DO IF NOT MISSING(V).
    COMPUTE NumC = CONCAT(RTRIM(NumC),",",STRING(V,F3.0)).
  ELSE.
    COMPUTE NumC = CONCAT(RTRIM(NumC),",").
  END IF.
END REPEAT.
*Get rid of initial ",".
COMPUTE NumC = LTRIM(NumC,",").
EXECUTE.
*********************************.

[Bruce Weaver]

Andy, I think you need to remove the ELSE portion of your DO IF structure to give the result the OP wants.  From the OP:

  ..should become a string variable with "101,155,322,102" not "101,155,322,,102".

In that case, DO IF could become IF, like this:

STRING NumC (A100).
DO REPEAT V = V1 TO V5.
  IF NOT MISSING(V) NumC = CONCAT(RTRIM(NumC),",",STRING(V,F3.0)).
END REPEAT.
*Get rid of initial ",".
COMPUTE NumC = LTRIM(NumC,",").
LIST NumC.

Result:

NumC
101,155,322,102


HTH.

Andy W wrote

You can use DO REPEAT to loop over the variables and sucessively concatenate values. Example below:

*********************************.
*Making fake data.
DATA LIST FREE / V1 TO V5.
BEGIN DATA
101 155 322 . 102
END DATA.

STRING NumC (A100).
DO REPEAT V = V1 TO V5.
  DO IF NOT MISSING(V).
    COMPUTE NumC = CONCAT(RTRIM(NumC),",",STRING(V,F3.0)).
  ELSE.
    COMPUTE NumC = CONCAT(RTRIM(NumC),",").
  END IF.
END REPEAT.
*Get rid of initial ",".
COMPUTE NumC = LTRIM(NumC,",").
EXECUTE.
*********************************.

[David Marso]

Could also use the python join  operator too;-)

Bruce Weaver wrote

Andy, I think you need to remove the ELSE portion of your DO IF structure to give the result the OP wants.  From the OP:

  ..should become a string variable with "101,155,322,102" not "101,155,322,,102".

In that case, DO IF could become IF, like this:

STRING NumC (A100).
DO REPEAT V = V1 TO V5.
  IF NOT MISSING(V) NumC = CONCAT(RTRIM(NumC),",",STRING(V,F3.0)).
END REPEAT.
*Get rid of initial ",".
COMPUTE NumC = LTRIM(NumC,",").
LIST NumC.

Result:

NumC
101,155,322,102


HTH.

Andy W wrote

You can use DO REPEAT to loop over the variables and sucessively concatenate values. Example below:

*********************************.
*Making fake data.
DATA LIST FREE / V1 TO V5.
BEGIN DATA
101 155 322 . 102
END DATA.

STRING NumC (A100).
DO REPEAT V = V1 TO V5.
  DO IF NOT MISSING(V).
    COMPUTE NumC = CONCAT(RTRIM(NumC),",",STRING(V,F3.0)).
  ELSE.
    COMPUTE NumC = CONCAT(RTRIM(NumC),",").
  END IF.
END REPEAT.
*Get rid of initial ",".
COMPUTE NumC = LTRIM(NumC,",").
EXECUTE.
*********************************.

[Bruce Weaver]

p.s. - You could add an ALTER TYPE command with AMIN to the end of Andy's code to reduce the length of NumC to whatever is required.

STRING NumC (A100).
DO REPEAT V = V1 TO V5.
  IF NOT MISSING(V) NumC = CONCAT(RTRIM(NumC),",",STRING(V,F3.0)).
END REPEAT.
*Get rid of initial ",".
COMPUTE NumC = LTRIM(NumC,",").
***********************.
ALTER TYPE NumC (AMIN).
***********************.
LIST NumC.

Bruce Weaver wrote

Andy, I think you need to remove the ELSE portion of your DO IF structure to give the result the OP wants.  From the OP:

  ..should become a string variable with "101,155,322,102" not "101,155,322,,102".

In that case, DO IF could become IF, like this:

STRING NumC (A100).
DO REPEAT V = V1 TO V5.
  IF NOT MISSING(V) NumC = CONCAT(RTRIM(NumC),",",STRING(V,F3.0)).
END REPEAT.
*Get rid of initial ",".
COMPUTE NumC = LTRIM(NumC,",").
LIST NumC.

Result:

NumC
101,155,322,102


HTH.

Andy W wrote

You can use DO REPEAT to loop over the variables and sucessively concatenate values. Example below:

*********************************.
*Making fake data.
DATA LIST FREE / V1 TO V5.
BEGIN DATA
101 155 322 . 102
END DATA.

STRING NumC (A100).
DO REPEAT V = V1 TO V5.
  DO IF NOT MISSING(V).
    COMPUTE NumC = CONCAT(RTRIM(NumC),",",STRING(V,F3.0)).
  ELSE.
    COMPUTE NumC = CONCAT(RTRIM(NumC),",").
  END IF.
END REPEAT.
*Get rid of initial ",".
COMPUTE NumC = LTRIM(NumC,",").
EXECUTE.
*********************************.

[Art Kendall]

Wondering what the context of this task would be.

It may be that there are other ways to reach your goal.

It appears that you are trying to save the data in .CSV format.

[Bruce Weaver]

Hi Art.  Excellent question about what the context is.  As we have seen many times before, that knowledge often helps lead to a much better solution.  

But in this case, I don't think that saving to .CSV will achieve what is intended, because the OP wants any missing variables to disappear.  For the sample data posted, saving to .CSV would give "101,155,322,,102", whereas the OP wants "101,155,322,102".

Having said that, one could save to .CSV, then import the .CSV file as text with one long string variable per case, and use REPLACE to replace all double commas with single commas.  But that seems quite a bit more cumbersome than Andy's method.

Cheers,
Bruce

Art Kendall wrote

Wondering what the context of this task would be.

It may be that there are other ways to reach your goal.

It appears that you are trying to save the data in .CSV format.

[Andy W]

I've done similar things with text strings in instances in which I wanted the final file to be flat. One example I made a spreadsheet that had crime incidents and multiple narratives concatenated (from dispatch, officers, detectives, etc.)

I put them all in the same field, so the spread sheet ended up looking like below:

100 Main St, Burglary, "DIS: ..... | DET: ....."

I did not want to have one incident spread across multiple rows. Another example is a popular way to store geometries anymore is in what are called well known text "WKT" format. In databases this just ends up being a free text field in a particular format that lists the outline of polygon.

(And thanks Bruce for the correction!)