topic Re: How to I extract a single value from a vector variable? in SAS Programming

How to I extract a single value from a vector variable?

normanbg — Wed, 06 Jan 2021 14:45:14 GMT

I imported some data from an Excel file with 5 lines and two columns (proc import datafile): ID and V. Say that ID is 1 2 3 4 5 and V is 2 4 6 8 10. I want to define x as the third entry of V; that is 6. In Fortran, that would be x=V(3); other languages use [ ] or some such thing. What about SAS?

I don't mean to select the case with ID=3, but to create a scalar variable with the single value 6. Seems simple enough, but I have looked everywhere (clearly, except for the right place), but without success.

Any help would be greatly appreciated.

Re: How to I extract a single value from a vector variable?

PaigeMiller — Wed, 06 Jan 2021 14:52:30 GMT

Are you talking about doing this in PROC IML, where vectors are a meaningful concept? Or are you talking about doing this in a DATA step, where there really are no such things as vectors? You can do this in either, but you really haven't indicated which you are using.

And what are you going to do with the value 6 once you have selected it?

Re: How to I extract a single value from a vector variable?

normanbg — Wed, 06 Jan 2021 16:13:47 GMT

Sorry. What I had in mind was to define the x In the Data step that follows the proc import and then use it as I would as any other numerical variable (y=x-p in another Data step, say, or in a proc print).

Re: How to I extract a single value from a vector variable?

PGStats — Wed, 06 Jan 2021 16:16:55 GMT

If you want x to be a number:

x = input(scan(V, 3), best.);

Re: How to I extract a single value from a vector variable?

PaigeMiller — Wed, 06 Jan 2021 16:32:34 GMT

So, if I understand properly

data constant;
    set have;
    if _n_=3 then do; 
        scalar=v;
        output;
    end;
    keep scalar;
run;
data want;
    if _n_=1 then set constant;
    set have;
    y=v-scalar;
run;

Re: How to I extract a single value from a vector variable?

Reeza — Wed, 06 Jan 2021 17:20:43 GMT

SAS doesn't really have concepts such as vectors, arrays or matrices within the data processing side, except within IML. Otherwise, it has data sets and macro variables that will persist between procs. This does mean you have to re-think how some things are done compared to other languages that are more matrix/vector/scalar based. And the terminology is of course different as well. If you're transitioning to SAS, it would be better to describe your business or base problem to help find the optimal solution otherwise you're very often going to be doing things inefficiently or running into the xy problem.
https://en.wikipedia.org/wiki/XY_problem

Re: How to I extract a single value from a vector variable?

normanbg — Wed, 06 Jan 2021 21:40:29 GMT

Thank you for your quick reply.
When I try to run your script and then print scalar, all I get is a blank. Obviously, I'm missing something. How about if I just try to extract the third entry of v in the data step and then print it? That should result is simpler code that I may be able to get to work.

Re: How to I extract a single value from a vector variable?

normanbg — Wed, 06 Jan 2021 21:44:40 GMT

Great, just a single line. Can't see where I could go wrong. But I did. When I tried it, and then printed V and x, I got the 5 correct entries for V and 5 blanks for x.

Re: How to I extract a single value from a vector variable?

normanbg — Wed, 06 Jan 2021 21:54:02 GMT

I don't think so. I designed a simple workaround and completed the project, but my inability to extract a value from a list (call it what you like) when I know its row and column coordinates, bothered me enough to pursue. Perhaps SAS sees the world differently, as you are implying, in which case I didn't find the solution because there isn't any—other than to restructure my Excel file, which was my workaround.

Re: How to I extract a single value from a vector variable?

PaigeMiller — Wed, 06 Jan 2021 21:59:49 GMT

@normanbg wrote:
Thank you for your quick reply.
When I try to run your script and then print scalar, all I get is a blank. Obviously, I'm missing something. How about if I just try to extract the third entry of v in the data step and then print it? That should result is simpler code that I may be able to get to work.

We don't know what you did. Please show us the SAS log for the code you used, and the results you got.

Re: How to I extract a single value from a vector variable?

PaigeMiller — Wed, 06 Jan 2021 22:25:45 GMT

@normanbg wrote:
Perhaps SAS sees the world differently, as you are implying, in which case I didn't find the solution because there isn't any—other than to restructure my Excel file, which was my workaround.

Of course there is a way to do this in SAS. There are many ways. I showed one such way.

Re: How to I extract a single value from a vector variable?

normanbg — Thu, 07 Jan 2021 12:32:28 GMT

What I did was to copy your script into a *.sas file and add a print. I included an additional line to check the suggestion offered by PGStats (Tourmaline); no joy in either case (see attached zip file, which contains the Excel file with the raw data and the Results tab in pdf format).

Re: How to I extract a single value from a vector variable?

PaigeMiller — Thu, 07 Jan 2021 12:43:14 GMT

Most of us will not download attachments.

Show us the log of your code by including it in your reply. Copy the log as text, paste it into the window that appears here when you click on the </> icon.

Re: How to I extract a single value from a vector variable?

normanbg — Thu, 07 Jan 2021 13:02:47 GMT

Here is a copy of the log file. (I didn't know about the reluctance to download attachments. Sorry. I'm a slow learner but I learn well.)

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         proc import datafile="/home/norman3/Norman/Yaacov/Norm.xlsx"
 74         dbms=xlsx out=have replace;
 75         run;
 
 NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with 
       options MSGLEVEL=I.
 NOTE: The import data set has 5 observations and 5 variables.
 NOTE: WORK.HAVE data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.01 seconds
       memory              2771.00k
       OS Memory           31400.00k
       Timestamp           01/07/2021 12:47:30 PM
       Step Count                        24  Switch Count  2
       Page Faults                       0
       Page Reclaims                     839
       Page Swaps                        0
       Voluntary Context Switches        18
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 76         data constant;
 77             set have;
 78             if _n_=3 then scalar=v;
 79          x = input(scan(V, 3), best.);
 80             keep x scalar;
 81         run;
 
 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       79:18   
 NOTE: There were 5 observations read from the data set WORK.HAVE.
 NOTE: The data set WORK.CONSTANT has 5 observations and 2 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              949.90k
       OS Memory           30124.00k
       Timestamp           01/07/2021 12:47:30 PM
       Step Count                        25  Switch Count  2
       Page Faults                       0
       Page Reclaims                     219
       Page Swaps                        0
       Voluntary Context Switches        9
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 82         data want;
 83             if _n_=1 then set constant;
 84             set have;
 85             y=v-scalar;
 86         run;
 
 NOTE: Missing values were generated as a result of performing an operation on missing values.
       Each place is given by: (Number of times) at (Line):(Column).
       5 at 85:8   
 NOTE: There were 1 observations read from the data set WORK.CONSTANT.
 NOTE: There were 5 observations read from the data set WORK.HAVE.
 NOTE: The data set WORK.WANT has 5 observations and 8 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              1307.03k
       OS Memory           30384.00k
       Timestamp           01/07/2021 12:47:30 PM
       Step Count                        26  Switch Count  2
       Page Faults                       0
       Page Reclaims                     166
       Page Swaps                        0
       Voluntary Context Switches        9
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           272
       
 
 87         proc print noobs data=want;
 88         var v scalar x y;
 89         run;
 
 NOTE: There were 5 observations read from the data set WORK.WANT.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           0.02 seconds
       user cpu time       0.02 seconds
       system cpu time     0.00 seconds
       memory              1943.40k
       OS Memory           31144.00k
       Timestamp           01/07/2021 12:47:30 PM
       Step Count                        27  Switch Count  0
       Page Faults                       0
       Page Reclaims                     675
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 
 90         
 91         
 92         
 93         
 94         
 95         
 96         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 108

Re: How to I extract a single value from a vector variable?

Ksharp — Thu, 07 Jan 2021 13:07:16 GMT

Try SAS/IML and calling @Rick_SAS

Re: How to I extract a single value from a vector variable?

Kurt_Bremser — Thu, 07 Jan 2021 13:15:46 GMT

 79          x = input(scan(V, 3), best.);
 80             keep x scalar;
 81         run;
 
 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       79:18

This indicates that variable V is numeric; the automatic conversion to character can have at most two words (one before and one after the period), so the result of the SCAN function will be empty. This means that the result of the INPUT function will be a missing value.

For reference, run this short code:

data test;
input x;
x1 = scan(x,1);
x2 = scan(x,2);
x3 = scan(x,3);
datalines;
1
1.5
-1.5
;

Log from that:

 73         data test;
 74         input x;
 75         x1 = scan(x,1);
 76         x2 = scan(x,2);
 77         x3 = scan(x,3);
 78         datalines;
 
 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       75:11   76:11   77:11

And you will see in the dataset that x3 is always empty.

Re: How to I extract a single value from a vector variable?

Kurt_Bremser — Thu, 07 Jan 2021 14:36:14 GMT

From your initial post, I guess that your dataset looks like this:

data have;
input id v;
datalines;
1 2
2 4
3 6
4 8
5 10
;

So, what is the expected result from this dataset?

Re: How to I extract a single value from a vector variable?

normanbg — Thu, 07 Jan 2021 14:26:18 GMT

Yes, that is indeed the data set. What I want to extract is the third entry of v, namely 6; that is, I expect (hope) that when I am finished fiddling, I will get x=6 with no blanks: in fortran terms, a scalar.

Re: How to I extract a single value from a vector variable?

normanbg — Thu, 07 Jan 2021 14:30:32 GMT

I don't have character variables in the input and I don't want character variables in the output. Please see my reply to your next post..

Re: How to I extract a single value from a vector variable?

normanbg — Thu, 07 Jan 2021 14:35:04 GMT

Why should that be necessary?