All SAS Data Management posts

Re: Merging rows based on date and location

cbagdon-cox — Tue, 11 Aug 2020 19:12:19 GMT

That almost worked perfectly. Tripped up on the blank end dates. Figured out a work around though of setting the blank end dates as 01/01/2099 so it pulled as the max end date and then changed those dates back to blank for the final output. Thank you!

Re: Merging rows based on date and location

novinosrin — Tue, 11 Aug 2020 18:38:05 GMT

proc sql;
create table want as
select fullname. min(start) as start format=date9.,max(end) as end format=date9.,
location
from your_table
group by location,fullname;
quit;

Merging rows based on date and location

cbagdon-cox — Tue, 11 Aug 2020 18:33:07 GMT

Trying to merge rows in which an individual was at the same location but has multiple date ranges resulting in multiple rows. Want to have one row per location with the first start date and last end date. Having a blank end date means they are still at that location and that would need to be kept.

I don't even know where to start with this merge. Open to DATA and PROC SQL steps. This data has approximately 500 rows currently for approximately 60 individuals. The reason the data populates like this is because there are separate bed assignments for each row, that level of data is not needed for this output, just location and the dates.

What my data currently looks like:

FULLNAME	START	END	LOCATION
SMITH, JOHN	14Sep2010	15Sep2010	CEDAR
SMITH, JOHN	15Sep2010	28Sep2010	CEDAR
SMITH, JOHN	28Sep2010	27Oct2010	CEDAR
SMITH, JOHN	28Oct2010	20Apr2011	OAK
SMITH, JOHN	20Apr2011	02May2011	OAK
SMITH, JOHN	02May2011	17May2011	OAK
SMITH, JOHN	17May2011	18Jul2011	PINE-A
SMITH, JOHN	18Jul2011	03Oct2011	PINE-A
SMITH, JOHN	03Oct2011	13Dec2011	PINE-A
SMITH, JOHN	13Dec2011	31Jan2012	OAK
SMITH, JOHN	31Jan2012	08Feb2012	OAK
SMITH, JOHN	08Feb2012	08Aug2012	OAK
SMITH, JOHN	08Aug2012	17Dec2012	OAK
MILLER, SALLY	21Aug2015	26Aug2015	PINE-B
MILLER, SALLY	26Aug2015	22Sep2015	CEDAR
MILLER, SALLY	22Sep2015	14Oct2015	CEDAR
MILLER, SALLY	14Oct2015	22Nov2015	CEDAR
MILLER, SALLY	22Nov2015	04Dec2015	CEDAR
MILLER, SALLY	04Dec2015	10Dec2015	CEDAR
MILLER, SALLY	10Dec2015	10Mar2016	OAK
MILLER, SALLY	10Mar2016	29Dec2017	OAK
MILLER, SALLY	29Dec2017	10May2018	OAK
MILLER, SALLY	10May2018	01Nov2018	OAK
MILLER, SALLY	01Nov2018		OAK
JOHNSON, ROBERT	16Apr2017	17Apr2017	MAPLE-A
JOHNSON, ROBERT	17Apr2017	08May2017	MAPLE-A
JOHNSON, ROBERT	08May2017	22May2017	MAPLE-B
JOHNSON, ROBERT	22May2017	31May2017	WILLOW
JOHNSON, ROBERT	31May2017	08Jun2017	WILLOW
JOHNSON, ROBERT	08Jun2017	02Aug2017	WILLOW
JOHNSON, ROBERT	02Aug2017	07Aug2017	MAPLE-C
JOHNSON, ROBERT	07Aug2017	11Sep2017	WILLOW
JOHNSON, ROBERT	11Sep2017	03Apr2018	WILLOW

What I want my data to look like:

FULLNAME	START	END	LOCATION
SMITH, JOHN	14Sep2010	27Oct2010	CEDAR
SMITH, JOHN	28Oct2010	17May2011	OAK
SMITH, JOHN	17May2011	13Dec2011	PINE-A
SMITH, JOHN	13Dec2011	17Dec2012	OAK
MILLER, SALLY	21Aug2015	26Aug2015	PINE-B
MILLER, SALLY	26Aug2015	10Dec2015	CEDAR
MILLER, SALLY	10Dec2015		OAK
JOHNSON, ROBERT	16Apr2017	08May2017	MAPLE-A
JOHNSON, ROBERT	08May2017	22May2017	MAPLE-B
JOHNSON, ROBERT	22May2017	02Aug2017	WILLOW
JOHNSON, ROBERT	02Aug2017	07Aug2017	MAPLE-C

Re: SAS DI Studio Impact Analysis View in table format

mohd-danshaik — Fri, 07 Aug 2020 05:39:14 GMT

Hi Allan,
Thanks a lot for your response. We do not have the Data Lineage exploration available to us. So that is why we are probably trying to get it done with code or some combination of Code plus VA exploration.

Re: SAS DI Studio Impact Analysis View in table format

mohd-danshaik — Fri, 07 Aug 2020 05:37:04 GMT

Hi Rama_V,

Thanks a lot for your inputs. This could be really helpful. I had thought of a similar way of extracting meta info and then representing them in SAS VA.
Will implement the same and keep you posted if it works

Re: SAS DI Studio Impact Analysis View in table format

Rama_V — Fri, 07 Aug 2020 02:26:13 GMT

Hi,

I have done something similar work in the past and presented the results on SAS VA. I have parts of solution you need stitch together to make it make work.

Job List from Metadata.sas - List all the jobs stored on SAS metadata created using DI Studio.

Job Source and Target Table.sas - List the tables used as source and Target table. You need to send dynamically the job uri from job list and get the data for every job. Also need modification to add Source and Target URI. Refer Base SAS and Metadata browser to understand the structure.

Table column inforamation.sas - This will give column information for all the tables on Metadata, so you need to control using Table URI from above.

Integrate all the data and present using any reporting tool. Can use exploration on VA to present impact analysis for lineage?

Hope this helps.

Rama

Re: ERROR ##-###: The INDEX function call has too many arguments.

PaigeMiller — Thu, 06 Aug 2020 17:12:38 GMT

wrote:

IF INDEX(UPCASE(var1), "OPANA" >0 or INDEX(UPCASE(var2), "OPANA")>0 or
INDEX(UPCASE(var3), "OPANA")>0 or INDEX(UPCASE(var4), "OPANA")>0 or
INDEX(UPCASE(var5), "OPANA")>0 or INDEX(UPCASE(var6), "OPANA")>0
THEN OPI_6=1;

IF INDEX(UPCASE(var1), "CODEINE" >0 or INDEX(UPCASE(var2), "CODEINE")>0 or
INDEX(UPCASE(var3), "CODEINE")>0 or INDEX(UPCASE(var4), "CODEINE")>0 or
INDEX(UPCASE(var5), "CODEINE")>0 or INDEX(UPCASE(var6), "CODEINE")>0
THEN OPI_16=1;

We also need to see the LOG, as I described above.

Re: ERROR ##-###: The INDEX function call has too many arguments.

Reeza — Thu, 06 Aug 2020 17:06:55 GMT

data want;
   set have;

   array v Var1-var6;

*make this big enough to hold all values;
length str $500.;
str = upcase(catx('-', of v(*)));

   opi3 = index(str, 'METHADONE')>0;
   opi6 = index(str, 'OPANA')>0;
   opi16 = index(str, 'CODIENE')>0;


run;

Also, if you have DINs there's drug listing DBs that you can use to link your drugs and they're categorized by categories, one of which is opioids. We're currently building out our PDMP so we're using the DB as it's constantly updated in our process.

The approach above concatenates all the values into one string, separated by a hypen (-), and searches that string. Then you need a single line of code to search that new string and create your flags. You COULD design a loop that goes over your diagnosis to make this more efficient if you have a ton of diagnosis.

Re: ERROR ##-###: The INDEX function call has too many arguments.

ballardw — Thu, 06 Aug 2020 16:30:00 GMT

If you are going to do such code frequently I suggest that you go ahead and make the case standard, either all lower or all upper so you can eliminate multiple Upcase(SSSS) the make code more readable.

And there are nicer ways to search for the same value among a bunch of variables:

data want;
   set have;
   array v Var1-var6;
   /* assumes all of the variables have been
      UPCASED prior
   */
   opi3 = ( whichc('METHADONE', of v(*))>0);
run;

The WHICHC, and numeric WHICHN functions return the position number that the first parameter, in this case the word METHADONE appears in a list of variables, using an array for short hand instead of listing var1, var2, var3, var4,var5, var6. If a value match is found the function will return the position in that list, i.e. 3 would mean that VAR3 matched, or zero otherwise. The surrounding ( >0) means the variable is assigned a comparison true/false result which is 1/0 numeric by default with SAS.

Fewer parentheses, fewer quotes, fewer commas and fewer OR mean that you are much less likely to miss one of them.

Re: ERROR ##-###: The INDEX function call has too many arguments.

DanielQuay — Thu, 06 Aug 2020 16:19:15 GMT

IF INDEX(UPCASE(var1), "OPANA" >0 or INDEX(UPCASE(var2), "OPANA")>0 or
INDEX(UPCASE(var3), "OPANA")>0 or INDEX(UPCASE(var4), "OPANA")>0 or
INDEX(UPCASE(var5), "OPANA")>0 or INDEX(UPCASE(var6), "OPANA")>0
THEN OPI_6=1;

IF INDEX(UPCASE(var1), "CODEINE" >0 or INDEX(UPCASE(var2), "CODEINE")>0 or
INDEX(UPCASE(var3), "CODEINE")>0 or INDEX(UPCASE(var4), "CODEINE")>0 or
INDEX(UPCASE(var5), "CODEINE")>0 or INDEX(UPCASE(var6), "CODEINE")>0
THEN OPI_16=1;

Re: ERROR ##-###: The INDEX function call has too many arguments.

PaigeMiller — Thu, 06 Aug 2020 16:16:43 GMT

Show us the log for this DATA step. We need to see everything in the log, code, NOTEs, WARNINGs, and ERRORs.

You need to preserve the formatting of the log by copying it as text and pasting it into the window that appears when you click on the </> icon. DO NOT SKIP THIS STEP!

Re: ERROR ##-###: The INDEX function call has too many arguments.

Reeza — Thu, 06 Aug 2020 16:16:17 GMT

Show the code that doesn't work. Showing working code doesn't help us isolate the issue, you likely forgot a quote, comma or bracket so pay attention to exactly where the error is indicated, SAS provides the line and column number for you to find it easily.

ERROR ##-###: The INDEX function call has too many arguments.

DanielQuay — Thu, 06 Aug 2020 16:03:39 GMT

I am working with data trying to identify opioid useage.

data want;

set have;

IF INDEX(UPCASE(var1), "METHADONE")>0 or INDEX(UPCASE(var2), "METHADONE")>0 or
INDEX(UPCASE(var3), "METHADONE")>0 or INDEX(UPCASE(var4), "METHADONE")>0 or
INDEX(UPCASE(var5), "METHADONE")>0 or INDEX(UPCASE(var6), "METHADONE")>0

THEN OPI_3=1;

run;

This code works for 14 of the 16 substances I am looking for.

But when I am looking for OPANA and CODIENE it is popping up with:

ERROR ##-###: The INDEX function call has too many arguments.

I think this may be something about how the data is formatted but I haven't had to use INDEX before.

Re: SAS DI Studio Impact Analysis View in table format

AllanBowe — Wed, 05 Aug 2020 15:20:52 GMT

You can extract this information using a recursive call to the metadata to explore the tree.

We have some documentation on it here: https://docs.datacontroller.io/dcu-lineage/

Advanced Data Lineage exploration is available at both table and column level in the SAS 9 version of Data Controller. You can export in PNG, SVG and CSV (table) formats. The tool is compatible from 9.3 onwards.

Column level version (interface is slightly different as the video is from an earlier version of the product). The CSV export is not shown but it's the same as the table-level one above, with additional columns for the.. columns.

A free version is available and we'd be happy to discuss - the contact form is here: https://sasapps.io

Re: SAS DI Studio Impact Analysis View in table format

mohd-danshaik — Wed, 05 Aug 2020 08:29:54 GMT

Hi Guys,

I am also currently working on a case study where in I need to extract the impact analysis details for any given table in SAS DI.

My question is pretty similar to the original question in this post but I would only want to check the data lineage for any given source table using SAS code.

I am currently using SAS 9.4

Re: How do I clear these errors?

wheddingsjr — Fri, 31 Jul 2020 15:50:54 GMT

Thanks RogerHed. The AGE should be Type = Numeric, length = 8 and the BMT should be Type = Character, Length = 1 in both datasets.Unfortunately, as a novice I searched the internet to learn how to do that but I was unsuccessful in finding the proper way to include that logic.

Re: How do I clear these errors?

wheddingsjr — Fri, 31 Jul 2020 15:44:51 GMT

Thanks all for your responses. The imported source document (excel), had a space between the heading "Discharge Date", that's why I wanted to do the rename. Instead I used a "_" between the two words and commented out the rename step, and it worked perfectly.

Re: How do I clear these errors?

TomKari — Fri, 31 Jul 2020 14:42:11 GMT

It looks to me like your first step (the PROC SQL) would work, except for one thing.

There are a number of uses of "Discharge Date", with a space between the two words. If this was a proper SAS variable, I think the code would work fine.

You say this is inherited code. As it is, there is no way this code will work. It looks to me like something happened to change a correct variable, possible "DischargeDate" or "Discharge_Date", to "Discharge Date". Has anything like that happened, and have any other changes been made?

You're probably best leaving the later steps until the earlier problems get solved. Problems tend to cascade.

Tom

Re: How do I clear these errors?

KurtBremser — Fri, 31 Jul 2020 10:17:09 GMT

Do not type code and logs manually; just copy/paste the text as is into the appropriate codeboxes.

What immediately catches my attention is the fact that there seem to be blanks in variable names. This is not correct syntax.

If you actually have blanks in variable names, you need to use so-called "name literals":

"Discharge Date"n

The spelling must be correct with regards to upper/lowercase. It is STRONGLY recommended to avoid this, so rename those variables immediately to valid SAS names (use underlines in place of blanks).

Re: How do I clear these errors?

RogerHed — Fri, 31 Jul 2020 09:10:46 GMT

No sorry, the Rename is confusing "Discharge Date1= Discharge Date"
Column name must come in pairs like x=y z=q.
Do you want to rename Discharge to Date1?

Re: How do I clear these errors?

RogerHed — Fri, 31 Jul 2020 09:04:08 GMT

The Sql:

In the Rename there is a Date=, remove the "=".
The Format "Date,5." I suppose is meant to be "date5.".

The Data step:

You have to decide if what type AGE and BMT must be if the two datasets are to be appended. A couple of choices:

I they mean the same thing, change into numeric or character in both datasets before appending. You can use a SQL View to do that before the datastep on the input ataset you want to alter into the append Data step.
If they mean different and are to be kept separate in the appended output, add a rename on one of the input datasets.

Re: How do I clear these errors?

wheddingsjr — Fri, 31 Jul 2020 06:51:05 GMT

My apologies, this did not present the way I typed it so I am going to attach the code and errors in a word document

How do I clear these errors?

wheddingsjr — Fri, 31 Jul 2020 06:43:35 GMT

Hello all

I have inherited SAS code that has approx. 20 steps and the code had errors throughout. I have been successful in cleaning it up until the last two steps. This is the code for the first one

PROC SQL;
CREATE TABLE WORK.AllClaims1 (DROP= Discharge Date Rename= (Discharge Date1= Discharge Date)) AS 
SELECT *
 ,INPUT(Discharge Date,5.) - 21916 AS Discharge Date1 FORMAT= mmddyy10.
FROM WORK.AllClaims
;QUIT;

This is the error message I get:

22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.

The is the code for the other step

DATA AllClaims_Updated;
SET hphc_EOL_FY20 WORK.AllClaims;
RUN;

This the error message that I get:

ERROR: Variable Age has been defined as both character and numeric.
ERROR: Variable BMT has been defined as both character and numeric.

These are the two columns that are effected and their types.

work.EOL_COHORT Age = Type Numeric, Length 8
BHM = Type Character, Length Character 1

AllCohorts1 Age = Type Character, Length Character 1
BHM = Type Numeric, Length 8

Any assistance with these would be greatly appreciated.

Re: sum one column and group by two others

Reeza — Thu, 30 Jul 2020 22:56:22 GMT

Look at the WEIGHT or FREQ statement within the PROC instead - and double check your numbers, especially if you have any missing values or non integers.

wrote:

Hello,

I have a big dataset with 3 values that are of interest. ID, Visite and WSL_Flaechen.

Visite ranges from 1 to 5 and WSL_Flaechen from 1 to 2.

With the following code i managed to group by Visite and count the WSL_Flaechen for each ID.

But instead i want count of WSL_Flaechen (blue arrow) to be multiplied with the value of WSL_Flaechen (red arrow) and display the sum of that for each ID.

So ID 4001 has

(1*8 + 2*12) = 32 on Visite 1 and

2 + 20 = 22 on Visite 2

and 26 on Visite 3

and so on...

The dataset only has 1s and 2s and the WSL_Flaechen field and i cant seem to just sum them up. I only display the count of 1s and 2s in seperate fields. How to i properly use the sum function here?

When i try to get sum in there i get the "statistical variable other than N without analysis variable" error.

Sincerely,

Keets

sum one column and group by two others

Keets — Thu, 30 Jul 2020 22:39:53 GMT

Hello,

I have a big dataset with 3 values that are of interest. ID, Visite and WSL_Flaechen.

Visite ranges from 1 to 5 and WSL_Flaechen from 1 to 2.

With the following code i managed to group by Visite and count the WSL_Flaechen for each ID.

But instead i want count of WSL_Flaechen (blue arrow) to be multiplied with the value of WSL_Flaechen (red arrow) and display the sum of that for each ID.

So ID 4001 has

(1*8 + 2*12) = 32 on Visite 1 and

2 + 20 = 22 on Visite 2

and 26 on Visite 3

and so on...

The dataset only has 1s and 2s and the WSL_Flaechen field and i cant seem to just sum them up. I only display the count of 1s and 2s in seperate fields. How to i properly use the sum function here?

When i try to get sum in there i get the "statistical variable other than N without analysis variable" error.

Sincerely,

Keets

Re: Computing the change in values across time and state

r4321 — Wed, 29 Jul 2020 20:25:23 GMT

Hello Ballard, I have updated my example code as per your instructions.

Re: /tmp Storage used for joining and sorting

TomKari — Wed, 29 Jul 2020 15:13:53 GMT

SAS DI is meant to be a managed environment for running production jobs. If you're having storage issues, you should discuss it with your SAS administrators to ensure changes are made to the environment to prevent these problems from happening.

Tom

Re: /tmp Storage used for joining and sorting

KurtBremser — Wed, 29 Jul 2020 14:38:16 GMT

You need to set UTILLOC in the sasv9_usermods.cfg file of the server context that DI uses (e.g. in the SASApp directory, not in SASApp/WorkspaceServer).

Re: /tmp Storage used for joining and sorting

AhmedAl_Attar — Wed, 29 Jul 2020 11:12:39 GMT

The default setting for the WORK library on Linux is /tmp. You can double check that setting by running the following statement in Code Editor from within SAS Data Integration Studio. Tools --> Code Editor

%put %sysfunc(pathname(work));

The result will be placed in the log tab.

To get around this issue, there are few options

Ask your SAS Administrator to redirect/change the -WORK setting to point to a different more sizable directory/file-system, by modifying the <SASHome>/SASFoundation/9.4/sasv9_local.cfg file
Create your own custom sasv9.cfg file and save it in your own $HOME directory on the Linux Compute Server(s)

Hope this helps,

Ahmed

Re: /tmp Storage used for joining and sorting

yabwon — Wed, 29 Jul 2020 09:26:27 GMT

Other approach would be to change the UTILLOC= System Option, see: https://documentation.sas.com/?docsetId=lesysoptsref&docsetTarget=p1texr4rxo0ipyn1ovajj11raccx.htm&docsetVersion=9.4&locale=en

But it is Configuration file/SAS invocation option so

-you will need admin help if you work with EG or

-if you could work with command line interface you could do it yourself.

Bart

Re: /tmp Storage used for joining and sorting

shrikantpwc — Wed, 29 Jul 2020 09:25:36 GMT

Thanks,

Can we perform this in SAS DI. By default Sort and join operation area is /tmp, and I want it to change to any other directory location.

Re: /tmp Storage used for joining and sorting

yabwon — Wed, 29 Jul 2020 09:20:18 GMT

HI,

I think you could use the USER library idea:

libname USER ("%sysfunc(pathname(work))" "/temp/");

It allow you to write codes like:

data OUTPUT;
  set sashelp.class;
run;

and the OUTPUT dataset will be stored in the USER library, so you don't have to change your code to much if you' were using "single name dataset"(i.e. without work. before dataset name) to store data in WORK.

But you have to remember that you have to clean USER up yourself after SAS session ends.

All the best

Bart

/tmp Storage used for joining and sorting

shrikantpwc — Wed, 29 Jul 2020 09:12:06 GMT

Hi Team,

Few of my jobs are getting failed due to insufficient storage, my work file location having sufficient space. /tmp/ getting utilised by job for sorting and joining.

my question is can we change the location of this sorting and joining location permentalty.

Thanks

-shrikant Suvarnkar

Re: Computing the change in values across time and state

ballardw — Wed, 29 Jul 2020 09:04:28 GMT

First, neither of those data step will run as posted. Datalines ONLY has the values. If you copy either of those to your editor and run the code you should get some errors. Pleases fix them. Datalines blocks end with a ;

And you should not have blank rows inside a datalines block either.

And did you understand at all what I said about grouping order? I can't follow your logic with the out of state order results of your example.

Re: Computing the change in values across time and state

r4321 — Wed, 29 Jul 2020 06:18:25 GMT

Anyone have some suggestions? Thanks!

Re: SAS DI Studio adds support for a Git plug-in

BilaTheLegend — Tue, 28 Jul 2020 20:09:11 GMT

Hi Anna and Chris,

how to configure the Git Plug-in to do not commit on branch master automatically but to redirect to a specific branch ?

Do you have an additional doc ?

Thanks in advance,

Re: Computing the change in values across time and state

r4321 — Thu, 30 Jul 2020 04:46:56 GMT

data have;
input time Bucketcode State $ Count;
datalines; 
101 222 OH 3
102 222 OH 4
103 222 OH 4
103 333 OH 1
104 333 OH 1
101 111 TX 2
102 111 TX 1
103 111 TX 1
104 111 TX 2
101 222 VA 1
102 222 VA 2
103 222 VA 2
104 222 VA 4
104 444 VA 2
;

data want;
input Time Bucketcode State $ Countdiff;
datalines;
102 222 OH 1
103 222 OH 0
103 333 OH 1
104 222 OH -4
104 333 OH 0
102 111 TX -1
103 111 TX 0
104 111 TX 1
102 222 VA 1
103 222 VA 0
104 222 VA 2
104 444 VA 2
;

Hello Ballard, yes, it is fine that the first state bucketcode is dropped as long as it appears in that first time period (i.e., 101). Appearances/disappearances of bucketcodes in subsequent time periods should be treated differently. If a new bucketcode appears in a time period (eg., 102) (that isn't the first) then that should be reflected in the changescore differential for that bucketcode/state/timeperiod triplet with whatever the addition in count was, eg., 1. If a bucket that was there in a time period disappears in a subsequent period (was there in 102, but not in a state-time-bucketcode triplet in the next period, eg., 103) then the state-time-bucketcode triplet should reflect whatever the drop in value is/was for that subsequent period. For example, VA-104-333 has a count of 2, but VA-105-333 is not there then VA-105-333 would be -2. Thank you again for your help.

Re: Computing the change in values across time and state

ballardw — Wed, 29 Jul 2020 09:00:01 GMT

So do you want the first state buckcode dropped or not I followed your original example which did so because that appeared to be the requirement. But if that is not a requirement and likely shouldn't then say so.

Please in some detail describe exactly what you wantto have happen with a bucketcode that disappears per "Conversely, a bucketcode that was there in OH during time period 103 might exit that state and not be there in time period 104. In both cases, the appearance / disappearances need to be marked in the change variable (e.g., +1, -4, etc.; see example)."

And I am not going to spend a lot time trying to make that verbiage match your data because the order you are displaying things gives me a headache. If you want to have things by "groups", then display them by groups. Then we don't have to try to hop around a bad order to tell when something may need to be inserted that wasn't there previously (which may be what I think you are requesting).

And Please provide the data in the form of a data step. I have shown you how to do that. And post the code in a code box opened with the </> icon.

Re: Computing the change in values across time and state

r4321 — Mon, 27 Jul 2020 07:29:21 GMT

Ballard, thank you for your quick reply. Your comment and code brought some things to my attention that I hadn't considered. I updated the notes to reflect these realizations.

Essentially, I do need the code to accomodate:

(1) I need it to accommodate multiple bucketcodes per state per time period.

(2) I need to account for the appearance / disappearance of bucketcodes within a state. For example, a new bucketcode might enter OH at time period 103 that wasnt previously there in the prior time period (102). Conversely, a bucketcode that was there in OH during time period 103 might exit that state and not be there in time period 104. In both cases, the appearance / disappearances need to be marked in the change variable (e.g., +1, -4, etc.; see example).

(3) Dropping the first time period is fine, but after that if a bucketcode enters a state and time period, that wasn't previously there, it should be treated as entering the dataset.

Thank you again

Re: Computing the change in values across time and state

ballardw — Sun, 26 Jul 2020 06:43:04 GMT

This appears to do what is requested except for output order.

data have;
input Time Bucketcode State $ Count;
datalines;
101  111  TX   2
101  222  VA   1
101  222  OH  3
102  111  TX   1
102  222  VA   2
102  222  OH  4
103  111  TX   1
103  222  VA   2
103  222  OH  4
104  111  TX   2
104  222  VA   4
104  222  OH  1
;

proc sort data=have;
   by state bucketcode time;
run;

data want;
   set have;
   by state bucketcode ;
   Countdif =dif(count);
   if not first.bucketcode;
run;

proc sort data=want;
   by time bucketcode state;
run;

I have used a different variable to hold the count difference for demonstration.

However, since you don't show any example data where a state has multiple bucketcodes no promises about more complex data.

The DIF function used is the difference between the current row's value of a variable and the previous row's value. Normally I would set the value for the first record of a group such as state

to missing but since you apparently are dropping the first record that isn't needed here.

If the final order is important you need to describe that as a requirement.

Computing the change in values across time and state

r4321 — Thu, 30 Jul 2020 04:45:52 GMT

Updated (7-27-20)

Hello all,

I could use your help figuring out some code. I have different 'buckets' that take on different values depending on the time, bucketcode, and state, and I am trying to compute the change in values (count) across time and state. Please see an illustration of my data below.

A couple new notes:

(1) I need it to accommodate multiple bucketcodes per state per time period.

(2) I need to account for the appearance / disappearance of bucketcodes within a state. For example, a new bucketcode might enter OH at time period 103 that wasnt previously there in the prior time period (102). If a new bucketcode appears in a time period (eg., 102) (that isn't the first) then that should be reflected in the changescore differential for that bucketcode/state/timeperiod triplet with whatever the addition in count was, eg., 1. If a bucket that was there in a time period disappears in a subsequent period (was there in 102, but not in a state-time-bucketcode triplet in the next period, eg., 103) then the state-time-bucketcode triplet should reflect whatever the drop in value is/was for that subsequent period. For example, VA-104-333 has a count of 2, but if VA-105-333 is not there then VA-105-333 would be -2.

(3) Dropping the first time period is fine, but after that if a bucketcode enters a state and time period, that wasn't previously there, it should be treated as entering the dataset.

data have;
input time Bucketcode State $ Count;
datalines; 
101 222 OH 3
102 222 OH 4
103 222 OH 4
103 333 OH 1
104 333 OH 1
101 111 TX 2
102 111 TX 1
103 111 TX 1
104 111 TX 2
101 222 VA 1
102 222 VA 2
103 222 VA 2
104 222 VA 4
104 444 VA 2
;

data want;
input Time Bucketcode State $ Countdiff;
datalines;
102 222 OH 1
103 222 OH 0
103 333 OH 1
104 222 OH -4
104 333 OH 0
102 111 TX -1
103 111 TX 0
104 111 TX 1
102 222 VA 1
103 222 VA 0
104 222 VA 2
104 444 VA 2
;

Thanks!

Selects which branch to use - SAS DATA INTEGRATION

kassialua1 — Wed, 22 Jul 2020 14:25:41 GMT

Hi,

I have branch structures at GIT.

Is there a way to control how SAS DI selects which branch to use? At the moment, all checkins end at our main branch (branch master), instead of the individual resource branch the developer is working on?

best regards

Kassia

Re: How do I import a sas7bdat file to SAS studio and then use it for data manipulation?

PaigeMiller — Mon, 20 Jul 2020 21:17:04 GMT

data want;

merge myfiles.dataset1 myfiles.dataset2 myfiles.dataset3 myfiles.dataset4;

by somevariablename; /* Optional */

run;

Re: How do I import a sas7bdat file to SAS studio and then use it for data manipulation?

jjoy4891 — Mon, 20 Jul 2020 21:09:04 GMT

please bear with me, have less exposure to sas programming

Re: How do I import a sas7bdat file to SAS studio and then use it for data manipulation?

jjoy4891 — Mon, 20 Jul 2020 21:03:41 GMT

thank you,
i did the above step, however, i can't merge these 4 datasets >

Re: How do I import a sas7bdat file to SAS studio and then use it for data manipulation?

PaigeMiller — Mon, 20 Jul 2020 20:56:47 GMT

You don't import the .sas7bdat files. Instead, you point SAS to the folder they are stored in by using a LIBNAME statement.

Something like this:

libname myfiles "/folders/myshortcuts/temp/temp";

How do I import a sas7bdat file to SAS studio and then use it for data manipulation?

jjoy4891 — Mon, 20 Jul 2020 20:48:12 GMT

Hi,

I have 4 sas7bdat files , wanted to concatenate them for data manipulation . How can i import the same to sas university edition and perform concatenation ?

Re: Transpose long to wide with Do Loop

Ksharp — Thu, 16 Jul 2020 13:04:13 GMT

Merge Skill:

data WORK.CLASS(label='Habitat Data');
infile datalines;
input Origin:8. Destination:8. Distance:8. Len:8. Vol:8.;
datalines;
1001 999 11891 26.44 193.00
1005 1016 2422 7.03 8.42
1005 944 19437 16.84 74.17
1010 1012 42986 22.10 262.95
1010 988 48093 9.10 26.36
1012 1010 42986 10.66 472.81
1012 988 44204 9.10 26.36
1012 999 47478 26.44 193.00
1016 1005 2422 30.73 62.22
;;;;

data temp;
 set class;
 by origin;
 if first.origin then n=0;
 n+1;
run;
proc sql noprint nowarn;
select distinct catt('temp(where=(n=',n,') rename=(
Destination=Destination',n,' Distance=Distance',n,' Len=Len',n,' Vol=Vol',n,'))') 
into : merge separated by ' '
from temp;
quit;
data want;
 merge &merge;
 by Origin;
 drop n;
run;

Re: Transpose long to wide with Do Loop

camper — Wed, 15 Jul 2020 17:59:55 GMT

Thank you Art! I appreciate your time!

Re: Transpose long to wide with Do Loop

camper — Wed, 15 Jul 2020 17:54:22 GMT

Thank you for taking the time to send your ideas! I don't know how he's going to use this crazy table, and I agree, it is very awkward!

Re: Transpose long to wide with Do Loop

camper — Wed, 15 Jul 2020 17:51:51 GMT

WOW. This is AMAZING! I am so grateful. You make it look so easy - and I could never have figured this out. Thank you so very much!!

Re: Transpose long to wide with Do Loop

camper — Wed, 15 Jul 2020 17:45:08 GMT

This worked great - Thank you! Thanks for the amazingly FAST reply! and thank you for including the link to the paper.

Re: Transpose long to wide with Do Loop

art297 — Wed, 15 Jul 2020 17:00:36 GMT

I'd recommend the %transpose macro to get what you want. e.g.:

proc sort data=class (rename=(Destination=Dest
                              Distance=Dist))
          out=need;
  by Origin Dist;
run;

filename tr url 'https://raw.githubusercontent.com/art297/transpose/master/transpose.sas';
%include tr ;

%transpose(data=need, out=want, by=Origin, Guessingrows=1000,
  var=Dest Dist Len Vol)

Art

Re: Transpose long to wide with Do Loop

ballardw — Wed, 15 Jul 2020 19:29:03 GMT

A data step solution while possible involves some more work. The first thing is to find out the maximum number of records with the same Origin. Then use that value to create the array dimension that are needed.

Then retain all of those.

Then using by processing you can reset the retained values when the first origin is encountered and then write output only on the last origin for each.

Question: How is this data set to be used? The desired output is a moderately awkward structure to do many things with.

If the sole purpose is to read things by a person it may be that a report would be better.

Consider:

proc report data=work.class;
   columns origin destination,(distance len vol);
   define origin /group;
   define destination/group across;
   define distance/ sum;
   define len/ sum;
   define vol/ sum;
run;

Which makes "columns" across but aligns the destinations so you can compare with different origins more easily. By have the values of distance, len and vol grouped under the destination it is much easier to see which destination is involved or to find the values involving any specific destination instead of the "scattered" inside the columns of the data set that might be created.

Re: Transpose long to wide with Do Loop

Jagadishkatam — Wed, 15 Jul 2020 16:50:56 GMT

Alternatively you may try the arrays as below

data WORK.CLASS(label='Habitat Data');
infile datalines;
input Origin:8. Destination:8. Distance:8. Len:8. Vol:8.;
datalines;
1001 999 11891 26.44 193.00
1005 1016 2422 7.03 8.42
1005 944 19437 16.84 74.17
1010 1012 42986 22.10 262.95
1010 988 48093 9.10 26.36
1012 1010 42986 10.66 472.81
1012 988 44204 9.10 26.36
1012 999 47478 26.44 193.00
1016 1005 2422 30.73 62.22
;;;;

data want;
set class;
by origin;
retain count dest1 dest2 dest3 Dist1 Dist2 Dist3 Len1 Len2 Len3 vol1 vol2 vol3;
array dest(3) dest1 dest2 dest3;
array Dist(3) Dist1 Dist2 Dist3 ;
array Lens(3) Len1 Len2 Len3;
array Vols(3) vol1 vol2 vol3;
if first.origin then count=1;
else count+1;
do i = 1 to 3;
if first.origin then do;
dest(i)=.;
Dist(i)=.;
Lens(i)=.;
Vols(i)=.;
end;
end;
dest(count)=Destination;
Dist(count)=Distance;
Lens(count)=len;
Vols(count)=vol;
if last.origin;
run;

Re: Transpose long to wide with Do Loop

Reeza — Wed, 15 Jul 2020 16:28:46 GMT

Your problem is the second example here exactly, expand it to 4 variables though.

Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

proc transpose data=class out=wide1;
by Origin;
var Destination;
run;

proc transpose data=class out=wide2;
by origin;
var distance;
run;

proc transpose data=class out=wide3;
by origin;
var length;
run;

proc transpose data=class out=wide4;
by origin;
var volume;
run;

data want;
merge wide1 wide2 wide3 wide4;
by Origin;
run;

Then once you have it in this format you can worry about re-ordering variables and then sorting as needed.

Does this get you closer?

Transpose long to wide with Do Loop

camper — Wed, 15 Jul 2020 16:23:15 GMT

Hello!

I've spent a couple of days researching forums and reading papers and I'm stumped. I don't know how to use arrays, macros, or do loops - and i think that's what's needed for this task. This is more complicated than I can do.

This is fish habitat data. The ORIGIN is the stream where the fish lives and the DESTINATION is another suitable stream. DISTANCE is the stream length between them, and the LEN and VOL are additional attributes. The data must be sorted by ORIGIN, DISTANCE because I want the transformed columns to be in the order of the closest destination habitats.

My dataset as >6000 obs, and some ORIGINs have 25 DESTINATIONS, so there will be about 100 columns in the resulting table. It's what the boss wants - and I'm bummed that I can't figure it out myself.

Here's the data:

data WORK.CLASS(label='Habitat Data');
infile datalines;
input Origin:8. Destination:8. Distance:8. Len:8. Vol:8.;
datalines;
1001 999 11891 26.44 193.00
1005 1016 2422 7.03 8.42
1005 944 19437 16.84 74.17
1010 1012 42986 22.10 262.95
1010 988 48093 9.10 26.36
1012 1010 42986 10.66 472.81
1012 988 44204 9.10 26.36
1012 999 47478 26.44 193.00
1016 1005 2422 30.73 62.22
;;;;

Here's the result I want:

As I move the data into the columns, I have abbreviated their variable names. I highlighted the DISTANCEs so you can see how they dictate the ordering of the columns.

Origin	Dest1	Dist1	Len1	Vol1	Dest2	Dist2	Len2	Vol2	Dest3	Dist3	Len3	Vol3
1001	999	11891	26.44	193.00
1005	1016	2422	7.03	8.42	944	19437	16.84	74.17
1010	1012	42986	22.10	262.95	988	48093	9.10	26.36
1012	1010	42986	10.66	472.81	988	44204	9.10	26.36	999	47478	26.44	193.00
1016	1005	2422	30.73	62.22

Thanks a lot! This has been fun, but a dead end. Hope someone out there has fun with it! I'm using SAS 9.4.

Re: Compute the number of observations and sum in a moving window

Thomas_mp — Sat, 11 Jul 2020 15:31:14 GMT

Good morning Kurt,

Thank you for your time. Your program does what I was expecting.

However, the program below (sent by other member of the community) corrects my own mistakes. This is: for the same individual/date (last two observations for individual b) we should have the same values for time and sum.

Thank you again,

Have a good weekend

Tomas

data k;

input X $ d y ;

informat d mmddyy8. ;

cards ;

a 01/01/96 5.1

a 10/27/96 2.3

a 12/16/96 8.1

a 01/05/97 9.6

a 01/11/97 1.6

b 02/03/97 6.2

b 03/25/97 5.8

b 11/15/98 2.6

b 01/14/99 4.9

b 01/29/99 3.7

b 03/10/99 4.6

b 03/10/99 2.3

c 03/16/99 9.6

proc sql;

create table want as

select *,

(select count(*) from k where x=a.x and d between a.d-100 and a.d) as times,

(select sum(y) from k where x=a.x and d between a.d-100 and a.d) as sum

 from k as a;

quit;

Re: Compute the number of observations and sum in a moving window

KurtBremser — Sat, 11 Jul 2020 09:41:03 GMT

See this:

data have;
input X $ d :mmddyy10. y;
format d yymmddd10.;
cards;
a     01/01/96    5.1
a     10/27/96    2.3
a     12/16/96    8.1
a     01/05/97    9.6
a     01/11/97    1.6
b     02/03/97    6.2
b     03/25/97    5.8
b     11/15/98    2.6
b     01/14/99    4.9
b     01/29/99    3.7
b     03/10/99    4.6
b     03/10/99    2.3
c     03/16/99    9.6
;

proc sql;
/* first, consolidate per day */
create table times as
  select
    x,
    d,
    count(d) as times,
    sum(y) as y
  from have
  group by x, d
;
/* now, calculate the rolling window */
create table want as
  select
    a.x,
    a.d,
    sum(b.times) as times,
    sum(b.y) as sum
  from times a
  left join times b
  on a.x = b.x and a.d - 100 le b.d le a.d
  group by a.x, a.d
;
quit;

b has a slightly different result, because I show each date only once.

Re: Compute the number of observations and sum in a moving window

ballardw — Fri, 10 Jul 2020 21:21:57 GMT

I hope you don't really want -999 in values in a SAS data set. If that "value" is supposed to indicate missing value then SAS has a perfectly good missing value (actually better, you can use . , .A through .Z and ._ to indicate different reasons why the value might be missing).

Unlike -999 the missing values would not be accidentally used in computations.

Re: Compute the number of observations and sum in a moving window

Reeza — Fri, 10 Jul 2020 21:51:54 GMT

Are you using SAS DI studio or one of the tools listed below?

Otherwise, this does belong in general programming forum.

Forum description for SAS Data Management:

SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop, SAS Data Preparation and others

wrote:
Hi Reeza,
it is the same question but with a few more explanations .. and perhaps it does not belong to the procedures, and fits better in data management part of SAS?

Re: Compute the number of observations and sum in a moving window

Thomas_mp — Fri, 10 Jul 2020 20:51:45 GMT

Hi Reeza,
it is the same question but with a few more explanations .. and perhaps it does not belong to the procedures, and fits better in data management part of SAS?

Re: Compute the number of observations and sum in a moving window

Reeza — Fri, 10 Jul 2020 20:32:59 GMT

Is this different than your question here: https://communities.sas.com/t5/SAS-Procedures/Find-out-the-how-much-a-person-spent-in-the-last-100-days/m-p/668458?

Compute the number of observations and sum in a moving window

Thomas_mp — Fri, 10 Jul 2020 20:20:51 GMT

Good morning,

I have observations of thousands of individuals (variable X for individuals a, b, c, d.. ) overtime (d) and the money each individual spent on that date (Y). The data are sorted by X and d

data k;

input X $ d y ;

informat d mmddyy8. ;

cards ;

a 01/01/96 5.1

a 10/27/96 2.3

a 12/16/96 8.1

a 01/05/97 9.6

a 01/11/97 1.6

b 02/03/97 6.2

b 03/25/97 5.8

b 11/15/98 2.6

b 01/14/99 4.9

b 01/29/99 3.7

b 03/10/99 4.6

b 03/10/99 2.3

c 03/16/99 9.6

; run;

I am trying to resolve two questions:

How many times one individual spent money in a period of 100 days? (this is the variable “times” in the solution below)
How much money has she/he spent in a the last 100 days? (this is “sum” in the solutions below.

So the question is to find out “times” and “sum”: the times an individual appears in the data base in the last 100 days, and the money spent in the last by this individual in the last 100 days.

For instance, for individual b:

She appears on 2/3/1997 for the first time, so times = 1; sum = 6.2

She appears again on 3/25/1997 , or 50 days later, so times = 2 (2/3/1997 and 3/25/1997) and sum is = 12 (5.8+6.2).

She appears again on 11/15/1998, or 300 days later, so times = 1 (because 300 > 100)) and sum is = 2.6.

She appears again on 1/14/1999, or 60 days later, so times = 2 and sum is = 2.6 + 4.9 = 7.5.

She appears again on 1/29/1999, or 15 days later, so times = 3 (3 times in 100 days, on 11/15/1998, on 1/14/1999 and on 1/29/1999. Sum is the value of Y on these 3 dates (3.7+4.9+2.6)

She appears again on 3/10/1999, or 40 days later, so times = 3 (3 times in 100 days, on 1/14/1999 on 1/29/1999 and on 3/10/1999. Sum is the value of Y on these 3 dates (3.7+4.9+2.3)

I did the complete solution in excel, below (and in the attached file). Diff is the number of days between two dates (I did this to get to the solution in excel; it may not be necessary). This difference is -999 when we change to a different individual.

In this case the solution is:

x	d	y	diff	times	sum
a	1/1/1996	5.1	-999	1	5.1
a	10/27/1996	2.3	300	1	2.3
a	12/16/1996	8.1	50	1	10.4
a	1/5/1997	9.6	20	2	20
a	1/11/1997	1.6	6	3	21.6
b	2/3/1997	6.2	-999	1	6.2
b	3/25/1997	5.8	50	2	12
b	11/15/1998	2.6	600	1	2.6
b	1/14/1999	4.9	60	1	7.5
b	1/29/1999	3.7	15	2	11.2
b	3/10/1999	4.6	40	3	8.3
b	3/10/1999	2.3	0	4	10.6
c	3/16/1999	9.6	-999	1	9.6

Thank you for your help,

Tomas

Compute the number of observations and sum in a moving window

Thomas_mp — Fri, 10 Jul 2020 20:19:20 GMT

Re: about table name longer than 32 character and data types in SAS

SASKiwi — Thu, 09 Jul 2020 23:28:21 GMT

- I can understand your disappointment. One of the reasons for extending the SAS 9.4 life cycle was to deal with the Adobe Flash Player end-of-life problem. A lot of effort had to be put into removal of Flash Player dependencies in 9.4 so customers could upgrade before the end of 2020. Once that is behind us, perhaps we will see some progress on other fronts.

Re: about table name longer than 32 character and data types in SAS

MRDM — Thu, 09 Jul 2020 22:43:29 GMT

Very disapointing that years later despite promises and a eta of probably by the end of 2018 there won't be a fix. Thanks for taking the time to reply anyway.

A viya trial is no good to me as we're not going to switch a national infrastructure with a few hundred users and I'm not the person who would make that decision, even more so when we are currently migrating to 9.4 on AWS instead of our own hosting. It looks like we're stuck with this issue, unfortunately I'll have to investigate other products that can support basic features like tables on databases which is a shame as I like SAS, but missing basic functionality is increasingly becoming an issue (such as xml processing much slower than ssis).

Re: about table name longer than 32 character and data types in SAS

TomKari — Thu, 09 Jul 2020 20:53:17 GMT

I concur with that there's a very good chance that correcting this is likely to happen first in Viya. I've been trying the Viya trial, and I'm finding it appears to be quite suitable for production work. I suggest you investigate it.

Tom

Re: about table name longer than 32 character and data types in SAS

SASKiwi — Thu, 09 Jul 2020 20:23:32 GMT

- As far as I'm aware the 9.4 life cycle was extended, currently still 9.4M6 but with 9.4M7 due out later in 2020. I haven't heard anything further regarding 9.5.

IMO most development effort now seems to be concentrated on the SAS Viya platform with a significant push to encourage SAS9 users to gradually migrate to the new cloud-ready technology. I wouldn't surprise me if the 32 character limit issue was fixed in Viya rather than SAS9.

Re: about table name longer than 32 character and data types in SAS

MRDM — Thu, 09 Jul 2020 08:40:22 GMT

Any update on this, it is now causing us major issues and the fix has been coming for years.

Export/Print bar graph along with table in HTML format using SAS Data integration

AkshayS — Wed, 08 Jul 2020 09:00:37 GMT

Hi All,

I am trying to print both table and bar graph in HTML but able to only get table in output using SAS DI.

Kindly assist me what modification is needed in my below code:

ods listing close;
ods html path='/ACTDATA/fireuw/' (url=none)
body='sgplot.html';
goptions reset=all;

PROC SQL;
create table CARS1 as
SELECT make, model, type, invoice, horsepower, length, weight
FROM
SASHELP.CARS
WHERE make in ('Audi','BMW')
;
RUN;
proc print;
quit;

proc SGPLOT data = cars1;
vbar length /group = type GROUPDISPLAY = CLUSTER;
title 'Cluster of Cars by Types';
run;
quit;

ods html close;
ods listing;

Thanks,

Akshay

Re: Column Header Validation

TomKari — Tue, 07 Jul 2020 20:21:13 GMT

Unfortunately, I don't have access to DI studio, so I'm not sure how these could be implemented.

If I were to try this in SAS code, I'd do one of the following:

1. Read in the file, but write the first line as a text string (if _n_ = 1) to one SAS dataset, and process the rest of the lines normally. In another step, do all of the checking needed on the header line, and then use macro processing to do the correct thing with the data.

2. Have a very basic data step, that splits the incoming dataset into two files, one with just the first line, and the other with all of the other lines. Then have a checking process to read in and check the one-line file, and set whatever macro variables are needed. Then process the other file, and based on the macro variables do something.

I hope maybe this gives you some thoughts for things that can be done in DI.

Tom

Column Header Validation

Flappyduck — Tue, 07 Jul 2020 13:46:26 GMT

Hi,

I'm looking for some help on creating some form of custom transformation to check the column headers a .csv file has when it get read into DI Studio.

I have a list of headers which should be in the table and when the file gets read in if it doesn't contain the correct headers, i want it will get moved to a check file so that the job doesn't fail. Any suggestions on the best way to set this up?

Thanks

Re: first observation is variable name

mkeintz — Mon, 06 Jul 2020 16:21:18 GMT

The double proc transpose is nice in that it has a simple structure, and it uses a pre-packaged sas routine.

But it may not scale well. Transposing 1,000,000 observations will create a dataset with 1,000,000 variables in the intermediate data set TEST1. The entire dataset would have to be processed two times, which can begin to use significant resources.

Re: Rearranging data in SAS 9.4

Thomas_mp — Mon, 06 Jul 2020 15:22:10 GMT

Thank you Diamond,

The Proc transpose works in this case.

One obstacle to implement your code is that I have 5000 variables Va, Vb,.... (etc),

But I appreciate you have taken time to help me,

Regards,

Tomas

Re: Rearranging data in SAS 9.4

Thomas_mp — Mon, 06 Jul 2020 15:17:57 GMT

Thank you Tom,

Regards,

Tomas

Re: Rearranging data in SAS 9.4

Ksharp — Mon, 06 Jul 2020 12:29:08 GMT

proc sql;
create table want as
select 'va' as label length=32,date,va as value from k
union all
select 'vb' as label length=32,date,vb as value from k
union all
.....................
quit;

Re: Rearranging data in SAS 9.4

TomKari — Sun, 05 Jul 2020 21:24:07 GMT

Thanks for posting the data step to make it easy!

proc transpose data=k out=ktrans;
	var va--vd;
	by date;
run;

proc sort;
	by _name_ date;
run;

Rearranging data in SAS 9.4

Thomas_mp — Sun, 05 Jul 2020 18:47:01 GMT

Good morning,

I need some help rearranging data in a very large data set using SAS 9.4.

I have the data as follows (please see also attached Excel file) :

data k;

input date va vb vc vd ;

cards ;

20000630 6.1207 8.7044 5.3283 5.846

20000929 6.1158 8.6934 5.3253 5.8423

20001229 5.7898 7.9828 5.1254 5.5832

20010330 4.9815 7.5266 4.4934 4.5082

;

run;

I would like to have the data as:

va	20000630	6.1207
va	20000929	6.1158
va	20001229	5.7898
va	20010330	4.9815
vb	20000630	8.7044
vb	20000929	8.6934
vb	20001229	7.9828
vb	20010330	7.5266
vc	20000630	5.3283
vc	20000929	5.3253
vc	20001229	5.1254
vc	20010330	4.4934
vd	20000630	5.846
vd	20000929	5.8423
vd	20001229	5.5832
vd	20010330	4.5082

Thank you for your help,

Tomas

Re: first observation is variable name

tocilj — Sat, 04 Jul 2020 20:44:18 GMT

Outdated reply but may help someone in future

/*----------------------------------------*/
data test;
infile datalines;
input A : $10. B : $3. C & $20. D : $20.;
datalines;
Name Sex Address Phone
Rachel F 789 Mid County 111-111-1111
;
run;

proc transpose data=test out=testt;
var A B C D;
run;

proc transpose data=testt out=testtt(drop=_:);
var col2;
id col1;
run;
/*----------------------------------------*/

Re: Quick datepart/date format query.

MRDM — Fri, 03 Jul 2020 15:33:40 GMT

Thanks, I got it working with the below, I had to change it to go Numeric to Chararter as Numeric to Numeric didn't work as on MONTHDAY it was still dropping leading zeroes (not an issue with YEARMONTH).

CASE WHEN DOB ^= . 
THEN cats(put(year(datepart(DOB)),z4.),put(month(datepart(DOB)),z2.))
ELSE "" END

I tried

put(datepart(dob),yymmn6.)

It got upset I think because it was going datetime to a straight numeric.

Re: Quick datepart/date format query.

KurtBremser — Fri, 03 Jul 2020 14:21:44 GMT

PS the PUT statement (used when writing to a text file) will honor the assigned format.

Re: Quick datepart/date format query.

KurtBremser — Fri, 03 Jul 2020 14:18:59 GMT

If you need the string, use

put(datepart(dob),yymmn6.)

For the Julian date (day in a year), use the JULIAN format

Re: Quick datepart/date format query.

MRDM — Fri, 03 Jul 2020 14:15:24 GMT

It's been exported to a delimited file so won't hold the formats anyway, if it was staying in a SAS/SQL table I agree, all I need is a string with the leading zero, I have 20055 already, I just need it to be 200505. I'm not sure that code will drop into the express on an extract and would need to be user written code instead? I've not really got time to start making changes to the layout of the job and more transformations, I was hoping to just edit my current expression slightly. Missing dates are already ignored as I exclude null dates in the first part.

Also if you think that's useless, they also want YEARDAY e.g. 200525 🤣

I tried:

CASE WHEN DOB  ^= .
THEN input(cats(year(datepart(DOB)),intnx('month',datepart(DOB),0,'b')),6.)
ELSE . END

20-10-1983 became 198386?

Re: Quick datepart/date format query.

KurtBremser — Fri, 03 Jul 2020 14:07:59 GMT

Why do you want a mostly unusable number, instead of a date with a proper format?

intnx('month',datepart(dob),0,'b') as newvar format=yymmn6.

All raw date values will be aligned to the first of the month, and missing values will automatically result in missing values.

Quick datepart/date format query.

MRDM — Fri, 03 Jul 2020 14:02:35 GMT

Hi, This might be fairly obvious but formats have always been my downfall and I'm up against the clock or I'd try experiment. I'm doing this in an expression on an Extract Transformation in DI Studio.

CASE WHEN DOB  ^= .
THEN input(cats(year(datepart(DOB)),month(datepart(DOB))),6.)
ELSE . END

Gives me 20055, I'd like 200505 (dates like 200512 are already fine). The output is a straight numeric rather than a date so I didn't think it would be an issue, from a quick google I found using z as the format so tried z6. but that didn't work.

Any ideas? Thanks

Re: Convert an excel data table into SAS format

saraphdnz — Wed, 01 Jul 2020 21:29:10 GMT

Thanks for your help. I am now able to run the program.

proc transpose
  data=mydata.return
  out=mydata.long (
  drop=_name_
  rename=(col1=y)
   where=(y ne .)
  )
 ;
by id;
var _:;
run;

Regards, Sara

Re: SAS Share and Power BI

akhozan — Tue, 30 Jun 2020 21:29:21 GMT

Thanks , we are not yet using SAS Viya, but that is still being planned/talked about.

Re: SAS Share and Power BI

SASKiwi — Tue, 30 Jun 2020 21:12:09 GMT

- FYI, I believe there will be other ways SAS and Power BI can work together when SAS Viya 4.0 is released later this year. There are some details of this in the SAS Global Forum 2020 presentations regarding the strategic partnership between SAS and Microsoft, which you can link to from the SAS Community.

Re: SAS Share and Power BI

akhozan — Tue, 30 Jun 2020 18:48:25 GMT

Thanks . I will work with my SAS Admin to get an ODBC port enabled for SAS/Share.

Re: SAS Share and Power BI

ChrisHemedinger — Tue, 30 Jun 2020 18:37:29 GMT

According to the Power BI doc, you can use the generic interfaces (ODBC or OLE DB) to connect to a variety of providers. SAS Workspace supports connections via OLE DB. SAS/SHARE has an ODBC connection method. Some of this might require configuration in your Power BI desktop environment. I don't know how/if these methods work in the cloud.

Re: SAS Share and Power BI

akhozan — Tue, 30 Jun 2020 18:18:54 GMT

Can you connect directly from Power BI to SAS using SAS/share via JDBC? We currently use this method with Spotfire, but Power BI will be the preferred visualization tool in the future once more of our data moves to the cloud.

Re: Move dataset from one library to other

Patrick — Tue, 30 Jun 2020 06:27:54 GMT

wrote:
In DIS, how to assign the table metadata to the new library?

The new library must also be defined in SAS Metadata. Then open the the table metadata properties where you can change the assigned library. And once done also move the table metadata object to the appropriate metadata folder.

Re: Move dataset from one library to other

David_Billa — Tue, 30 Jun 2020 02:52:39 GMT

In DIS, how to assign the table metadata to the new library?

Re: Move dataset from one library to other

Patrick — Tue, 30 Jun 2020 00:54:00 GMT

wrote:
Is there a way that we can move the dataset from one library to other library?
In one of my DI job, I want to change the library of the source dataset. Any help?

In DIS assign the table metadata to the new library.

Redeploy all impacted jobs (impact/reverse impact analysis on the table will give you the list of jobs).

With EG use Proc Copy or Proc Datasets/Copy to also move the physical data. Consider using the MOVE option in the Proc if you don't want to just replicate (copy) the data but also remove it from the source.

Re: Move dataset from one library to other

SASKiwi — Mon, 29 Jun 2020 19:39:55 GMT

Correct. A PROC COPY by itself will not change library metadata. If you want the copied dataset registered in metadata in the library it was copied to then you have to do that separately.

Re: Move dataset from one library to other

David_Billa — Mon, 29 Jun 2020 19:27:39 GMT

Yes.

I would like to know if I do proc copy from SAS EG, it will change the
library of the respective dataset which was being used in DI studio job
without updating metadata?

Re: Move dataset from one library to other

PaigeMiller — Mon, 29 Jun 2020 19:19:13 GMT

I don't understand the question.

PROC COPY takes a dataset(s) in one library and makes a copy in the other library. Is that what you want?

Re: Move dataset from one library to other

David_Billa — Mon, 29 Jun 2020 19:06:39 GMT

If I do PROC COPY via SAS EG, it will update the library in the respective
dataset which was being used in the job?

Re: Move dataset from one library to other

PaigeMiller — Mon, 29 Jun 2020 18:59:17 GMT

PROC COPY