topic Re: Can I Select * in Query Builder, or Trim an individual column when using a select * statement? in SAS Enterprise Guide

Can I Select * in Query Builder, or Trim an individual column when using a select * statement?

Boswser — Mon, 18 Oct 2021 12:09:49 GMT

I have a run I inherited that transposes data. For some reason, this transpose sticks 30+ empty spaces in front of the NOV2018 values, so the requester has to trim it and has asked if I can prevent that issue.

I inserted a Query Builder step to trim, but noticed that the number of columns generated by the run depends on the data. So the columns I grab with the query builder are not always there, causing the run to fail.

I’m looking into using PROC SQL language to achieve this instead of QB, but I am running into a similar issue. I need to use SELECT * to make sure I grab each column that’s created, but I can’t trim down the date column without naming it in the SELECT statement. If I name the columns, I run into the same problem as QB.

Is there a way to SELECT * in QB or PROC SQL and still be able to trim a named column?

Re: Can I Select * in Query Builder, or Trim an individual column when using a select * statement?

Reeza — Mon, 18 Oct 2021 15:26:22 GMT

No, if you use * you cannot TRIM the same column in the same step. You can in the next step.

Re: Can I Select * in Query Builder, or Trim an individual column when using a select * statement?

Boswser — Mon, 18 Oct 2021 15:45:30 GMT

I understand, that’s what I figured. Unfortunately if I do a new step where I need to name all the columns in order to trim one (since I still need all the columns), it creates the same issue—missing any new columns that may have been created in the initial data pull.

My workaround for this right now is to so SELECT *, date_column so that I have two copies of the date column and I am able to trim the one named in the select statement. Better than nothing!

Re: Can I Select * in Query Builder, or Trim an individual column when using a select * statement?

HB — Mon, 18 Oct 2021 17:02:45 GMT

Then you can do a select from the two column result down to single column.

Re: Can I Select * in Query Builder, or Trim an individual column when using a select * statement?

ballardw — Mon, 18 Oct 2021 18:08:43 GMT

@Boswser wrote:
I have a run I inherited that transposes data. For some reason, this transpose sticks 30+ empty spaces in front of the NOV2018 values, so the requester has to trim it and has asked if I can prevent that issue.

I inserted a Query Builder step to trim, but noticed that the number of columns generated by the run depends on the data. So the columns I grab with the query builder are not always there, causing the run to fail.

I’m looking into using PROC SQL language to achieve this instead of QB, but I am running into a similar issue. I need to use SELECT * to make sure I grab each column that’s created, but I can’t trim down the date column without naming it in the SELECT statement. If I name the columns, I run into the same problem as QB.

Is there a way to SELECT * in QB or PROC SQL and still be able to trim a named column?

Perhaps time to look at the code doing the TRANSPOSE of the data and address the issue at the start instead of fixing later.

Re: Can I Select * in Query Builder, or Trim an individual column when using a select * statement?

Boswser — Mon, 18 Oct 2021 18:47:44 GMT

It’s done in a Transpose step in SAS EG. I did look at the code of the step and there’s nothing there indicating addition of spaces as far as I can tell. Though I admit I am unfamiliar with PROC TRANSPOSE.

The code starts with a PROC SORT, and the PROC TRANSPOSE looks like this:

DATA=WORK.SORTTempTableSorted
OUT=WORK.TRNSTransposed(LABEL=“Transposed WORK.TEST2”)
PREFIX=Column
NAME=Source
LABEL=Label
;
BY Col1 Col2 Col3 Col4;
VAR name_column date_column;

Is anything in there responsible for the added spaces in date_column?

Re: Can I Select * in Query Builder, or Trim an individual column when using a select * statement?

Boswser — Mon, 18 Oct 2021 18:49:55 GMT

I’m afraid this still hits the same issue—I’d need to name every column if I was going to grab all except one, right? So I wouldn’t be using SELECT *. So if that day’s data generated a different # of columns, I would miss them since they would not be named in my SELECT statement.