topic Re: Concat 2 numeric column values to 1 numeric (!) value seperated by dash? in SAS Programming

Concat 2 numeric column values to 1 numeric (!) value seperated by dash?

SAS_Question — Sun, 25 Feb 2018 15:06:53 GMT

Hi everyone who is reading this 🙂

I have 1 char colum (source) with values (D_RangeL and D_RangeH) and 1 numeric column with values (2,81 and 9,37 = european notation, no dot, but comma!). See the picture where it says Have.

Now what I want to do in Base SAS is: I want to concatenate the row values to 1 value. So it is like in the picture where it says WANT.

Is that possible in SAS? I did this example in Excel, as you might have noticed...because in SAS I can;t manage this.

I used

catx(' - ', RangeL, RangeH);

but because this becomes a char value and I want it in a Num column this is not possible in SAS? Or...

Re: Concat 2 numeric column values to 1 numeric (!) value seperated by dash?

Reeza — Sun, 25 Feb 2018 15:15:14 GMT

It’s not possible to have characters in a numeric variable.

Depending on what you’re ultimately trying to do there may be other workarounds, but CATX is the correct approach for a character variable.

Excel doesn’t force types on columns, which is why it’s allowed there but in general most Datsbass systems don’t allow this type of thing.

@SAS_Question wrote:

Hi everyone who is reading this 🙂

I have 1 char colum (source) with values (D_RangeL and D_RangeH) and 1 numeric column with values (2,81 and 9,37 = european notation, no dot, but comma!). See the picture where it says Have.

Now what I want to do in Base SAS is: I want to concatenate the row values to 1 value. So it is like in the picture where it says WANT.

Is that possible in SAS? I did this example in Excel, as you might have noticed...because in SAS I can;t manage this.

I used
catx(' - ', RangeL, RangeH);
but because this becomes a char value and I want it in a Num column this is not possible in SAS? Or...

Re: Concat 2 numeric column values to 1 numeric (!) value seperated by dash?

SAS_Question — Sun, 25 Feb 2018 15:21:06 GMT

Thank you for your confirmation. That's what I was afraid for...

Now, that we know that Excel doesn't matter what you put in the cell (char of num), is it possible to do this when I export the SAS table data to excel?

Can I say to SAS, when you come across row (D_RangeL and D_RangeH) keep the value in mind, and when exporting export these values as one row and as a char: "D_RangeL - D_RangeH" (in values: 2,81 - 9,37)

I really don't know where to start here.. 🙂

Some help would be nice guys. Thanks again.

Re: Concat 2 numeric column values to 1 numeric (!) value seperated by dash?

Reeza — Sun, 25 Feb 2018 15:28:18 GMT

Excel won’t consider it a numeric variable either, but it doesn’t force a column to adhere to rules. you can check that in Excel using ISNUMBER() function.

You can export the values to Excel, is that not working?

If you need help you need to explain what you’re trying to do, what you have or want. So far all you’ve asked is if things are possible.

Also, not a guy.

@SAS_Question wrote:

Thank you for your confirmation. That's what I was afraid for...

Now, that we know that Excel doesn't matter what you put in the cell (char of num), is it possible to do this when I export the SAS table data to excel?

Can I say to SAS, when you come across row (D_RangeL and D_RangeH) keep the value in mind, and when exporting export these values as one row and as a char: "D_RangeL - D_RangeH" (in values: 2,81 - 9,37)

I really don't know where to start here.. 🙂

Some help would be nice guys. Thanks again.

Re: Concat 2 numeric column values to 1 numeric (!) value seperated by dash?

SAS_Question — Sun, 25 Feb 2018 17:42:27 GMT

Hi Reeza,

sorry about that 'guys' statement.. it is a thing i always use.

Consider it a statement of concatenation of boys and girls, though this is also not possible! 😉

As for the export:

Just consider the 2 given columns in the picture and the 2 rows, which need to be exported like on the picture where it says WANT.

That's what I want to do.

What I do to get these kind of table is (very simplified now):

I import 2 csv files. Do some sorting and selection on them by proc sort and proc sql.
After that I get a table which I have to transpose (thus getting automatically num and char columns).

Till then everything is going okay.

So supose then this is a very simplified transposed table which I get, and which I can succesfully export to my excel sheet:

data x;
infile datalines delimiter=','; 
   input source $10. value;
   datalines;                      
		D_RangeH, 10
		D_RangeL, 9
;
run;

proc sort data=x;
  by source descending value;
run;

proc export data=x outfile="p:\temp\test.xlsx" dbms=xlsx replace;
run;

No problems so far.

But now, dear friends, what I want to do is concat the 2 rows

D_RangeH 10
D_RangeL 9

To 1 Row, like this:

D_RangeLH 9 - 10

But I really dont have a clue where to start..

So any help with this step would be nice. Thank you in advance.

I hope you understand what I mean now. If not, let me know again.

Re: Concat 2 numeric column values to 1 numeric (!) value seperated by dash?

Reeza — Sun, 25 Feb 2018 17:44:46 GMT

After that I get a table which I have to transpose (thus getting automatically num and char columns).

Till then everything is going okay.

I don't see any transpose in your code, but you say that's going okay? Did you miss including the PROC TRANSPOSE?

Re: Concat 2 numeric column values to 1 numeric (!) value seperated by dash?

SAS_Question — Sun, 25 Feb 2018 17:47:43 GMT

Consider this simplified step:

data x;
infile datalines delimiter=','; 
   input source $10. value;
   datalines;                      
		D_RangeH, 10
		D_RangeL, 9
;
run;

as the result for all my steps (incl. the transpose step). So that's all okay.

No problems there.

The question is:

How do I concatenate the 2 row values to 1 row with concatenated value of 9 - 10.

That's all I want to know, in fact. Excuse all the other context! 🙂

Re: Concat 2 numeric column values to 1 numeric (!) value seperated by dash?

Tom — Sun, 25 Feb 2018 17:56:42 GMT

You will need to create a CHARACTER variable to hold a string like that.

You can use the COMMAX format to generate numbers that use comma instead of period as the decimal point. Although it might be based on your LOCALE setting that COMMA format will do that for you instead.

data have ;
  input name $ value ;
cards;
RangeL 2.81
RangeH 9.37
;
proc transpose data=have out=wide (drop=_name_) ;
  id name ;
  var value ;
run;

data want ;
  set wide ;
  length cvalue $30 ;
  cvalue = catx(' - ',put(rangel,commax10.2),put(rangeh,commax10.2));
  format range: commax10.2 ;
run;

proc print width=min;
run;

       Range    Range
Obs      L        H        cvalue

 1     2,81     9,37     2,81 - 9,37

Re: Concat 2 numeric column values to 1 numeric (!) value seperated by dash?

Reeza — Sun, 25 Feb 2018 17:58:03 GMT

Your code doesn't work. Please test it in the future.

Here's one way.

data want;
merge x (obs=1)
    x (firstobs=2 rename= (value=prev_value source=prev_source));
    
    want = catx('-', prev_Value, value);
run;

Re: Concat 2 numeric column values to 1 numeric (!) value seperated by dash?

SAS_Question — Sun, 25 Feb 2018 18:51:59 GMT

Again sorry @Reeza: the code worked in Enterprise Guide here... Maybe not in Base SAS only?

@Tomand @Reeza both very beautiful solutions provided.

Now how do I select both as solution for my question?

Help! 🙂

You both deserve it!