About ErikLund_Jensen

ErikLund_Jensen · ‎03-22-2023

Hi @SASMom2 As noticed by @PaigeMiller , your first statement is syntactically wrong, because you cannot combine two %if conditions with an OR-operator. You don't need the double quotes, because they are not acting as quotes in the macro language. They are treated as text and becomes part of the value. So when used on both sides in the equation,the result will be the same. You can use the trailing . after a macro variable name, but it not necessary. It acts as a delimiter between a macro variable name and something immediately following it, so this something is not seen as part of the macro variable name. As for your second statement, you can also use the in operator in macro language, but you need a MINOPERATOR option to tell the macro compiler that it should accept it. So these four macros all work and give the same result: %macro typ(cust=); %if "&cust"="abc" or "&cust"="def" %then %put TRUE - Cust is &cust; %else %put FALSE - Cust is &cust; %mend; %macro typ(cust=); %if &cust=abc or &cust=def %then %put TRUE - Cust is &cust; %else %put FALSE - Cust is &cust; %mend; %macro typ(cust=) / minoperator; %if "&cust." in ("abc" "def") %then %put TRUE - Cust is &cust; %else %put FALSE - Cust is &cust; %mend; %macro typ(cust=) / minoperator; %if &cust. in (abc def) %then %put TRUE - Cust is &cust; %else %put FALSE - Cust is &cust; %mend; %typ(cust=abc) ; %typ(cust=def) ; %typ(cust=DEF) ; %typ(cust=ghi) ;

ErikLund_Jensen · ‎03-19-2023

Hi @neyoushah To elaborate on the post from @Quentin SAS is basically a statistical package with procedures that works on SAS Data sets, and the data step is originally intended as a tool to get data into SAS from external sources. And while it is true that almost anything can be done with a single complicated data step, is is not always the best approach. This forum is about programming, so it is natural that most problems are presented as programming problems, i.e. how to get a result based on the idea that a solution starts with the token data and ends with the token run, and that most answers are given within that same context. Unfortunately there is no forum to address overall program design, meaning how to write a whole program, not a data step, to solve a given problem with SAS software. A data step can easily be so complicated that nobody (including the programmer herself after a week) can figure out what's going on. Start with getting an understanding of what you have. In this case it seems that there is one record per patient, and that each D_I10_ column represents a visit with one diagnosis code per visit, which may be alcohol related. If more than one column with the same code is present, they represent one visit each. If you are in doubt, or your interpretation in any way conflicts with actual data, get back to the source of data to verify it. Then define your final goal. What is it you (or the one requesting it) wants to know based on data. Which final data structure will be best to obtain that. Next step is to get data into SAS. If the original source is a spreadsheet, then write a data step to read it into SAS. Just get it in, leave further data manipulation to later. And don't rely on Proc Import for this. Then consider what additional information you need. In this case it is a list of alcohol-related diagnosis codes. Don't think of hardcoding them as constants in select-constructs or if-then sequences. Get them into a SAS data set too, so they are ready for further use, if necessary as cards in a data step. Now consider your program flow. Don't be afraid of multiple steps, it is much better to break the problem down so it can be solved with a series of steps, where you for each step can chosse the best tool for that. Way back "real programmers" mocked SAS as meaning Sort-After-Sort, but it's the way it works, and what makes it much faster to get a result with SAS than with more code-oriented languages. So use it as intended. First thing is to get rid of the Excel-structure, which has no place in SAS, so transpose your SAS input from wide to long. Use Proc Transpose because it is simpler than traversing arrays in a data sptep. The transposed data set is much easier to handle, now you can drop unwanted diagnisis codes with a simple Proc SQL join or a Data step merge, whichever you prefer, because you have another data set with the list of relevant codes. Then consider the next step. Is it the number of alcohol-related visits per patient you want, the number of visits per diagnosis code, or the number of different diagnosis coder per patient. If it is more than one of these, then split up from here and treat different wishes as individual subtasks. It is much easier to code, if you can get the result with a simple count in Proc SQL og a Proc Means. This approach gives a program that is much easier to code and understand, easier to maintain and easier to add additional functionaly to. Just add other steps to fulfill further requests. I have so many old programs running today where I deeply regret that I started out with coding instead of stepping back to make a proper design based on an understanding of data. The time spent on planning comes back twice in the coding phase. Besides, it is much easier to verify that the code works as intended, because there are intermediate result to check and small pieces of code to debug and correct. So don't be afraid of multiple steps. And remember that the core procedures are parts of the SAS language too, and that a good understanding of them is an integral part of being a good SAS programmer.

ErikLund_Jensen · ‎03-19-2023

Hi @Nietzsche Sorry, my text seems to have dissappeared. There is no start= option. The end=xxx option sets the value of variable xxx to 1 (true) when the current observation is the last observation read into the program vector. The automatic variable _N_ holds the number of the current observation read into the program vector, so if _N_ = 1 is true in the first observation read. Neither the variable created by the end= option nor the automatic variable _N_ are written to the output data set. Note that the data set options firstobs= and obs= control the observations read into the program vector and are applied first, so end= and _N_ works on the resulting subset. Try the code in the previous post and see what happens.

ErikLund_Jensen · ‎03-19-2023

data a; do obs = 1 to 5; output; end; run; data b; set a end=eof; if _N_ = 1 then firstobs = 1; if eof then lastobs = 1; run; data c; set a (firstobs=2 obs=4) end=eof; if _N_ = 1 then firstobs = 1; if eof then lastobs = 1; run;

ErikLund_Jensen · ‎03-16-2023

Hi @Cmags You can do that. The general receipe is the following: Get a list of the available Excelfiles in the folder: There are plenty of how-to-exampes in this forum. Write a small macro to loop over the file list and allocate the file as an XLSK-libname Use proc copy to copy the excel-sheet to a table in your output SAS libname.

ErikLund_Jensen · ‎03-08-2023

Hi @SAS_INFO May I suggest a small improvement of the code supplied by @russt_sas . I have added three lines to the code, so the output now contains the file name and line number besides the lines containing one of the search strings. (+ changes in 2 input formats to handle longer strings based on my actual test input), %macro search(dir,string); filename listing pipe "dir &dir\*.sas /b"; data new; infile listing truncover ; input filename $255.; file = filename; filename="&dir\" || filename; infile dummy filevar=filename end=done truncover; obsnum = 0; do while (not done); input line $char255.; obsnum + 1; if prxmatch("m/&string/oi",line) > 0 then output; end; run; %mend search;

ErikLund_Jensen · ‎03-02-2023

Hi @DarrylLawrence It would be interesting to se the result of rerunning with a data _null_ step instead, so no time is used for compressing and writing output, just to see if the where clause is the real problem here.

ErikLund_Jensen · ‎03-01-2023

Hi @Pili1100 If I got this right, you want to automate the process of copying tables from Production to Test, so you have updated source data available for testing a given job. How do you want the solution to work? - I would recommend a solution that works within DI Studio in tne Test environment. A utility job containing a user-defined transformation, where the user can copy-paste one or more Job-ID's from the Basic Properties pane below the Folder Tree into the Transformation's Options-tab. The transformation should do the following: Extract a list of all source tables with physical paths for the given Job-ID's, and then Loop over the tables and for each table, allocate a libname to the Test Path, another to the Production Path and copy the table using Proc Copy. This will work if: the tables are SAS Base datasets (sas7bdat-files), the physical folder structures are identical in Test and Production, so the Production path can be derived from the Test path, the physical Production folder is accessible from the Test environment, and the user has permission to read the physical dataset. Getting the list of all source tables with physical paths from metadata is a bit complicated, but not impossible, anf much easier than trying to get the information out of the generated source code. I can help with some code if you are interested, only give me a couple of days because I don't have anything ready that does exactly that. The metadata part will give you the necessary information, so you could implement a solution as a DI Studio job or wharever you prefer.

ErikLund_Jensen · ‎02-27-2023

Hi @supersasnewbie As it says, use Proc datasets with Repair: libname mylib "c:\testdata"; proc datasets lib=mylib; repair myfile; run; quit; In most cases, running a Proc Content on the library will also do the trick, but Proc Datasets is the secure way.

ErikLund_Jensen · ‎02-25-2023

Hi @mariko5797 This vill do it, given the input must be sorted in ID and VISNO: data want; set have (drop=RESULT); by ID; length VISITS $200; retain VISITS; VISITS = catx(',',VISITS,VISNO); if last.ID then do; output; VISITS = ''; end; run;

ErikLund_Jensen · ‎02-24-2023

Hi @bej You don't specify if your SPSS files are export files from SPSS or SPSS data files. If they are data files, this article gives a lot of helpful information about importing in SAS, both with Base SAS and with SAS Enterprise Guide, which has a point-and click task for SPSS import. https://communities.sas.com/t5/SAS-Communities-Library/How-to-import-SPSS-data-files-into-SAS/ta-p/238581

ErikLund_Jensen · ‎02-19-2023

@Quentin @ChrisHemedinger Thanks for clarifying (and to Chris: thanks for your guest appearance at our local Danish SAS Platform Meeting this month - very interesting and informative. Do you have a graph or table showing posts/country). I am aware that M8 is available now, so V9 isn't going into immediate retirement. But our SAS representative has informed us about M8 being the last maintenance update with end of support in 2028. In our organization - a big municipal government - it may take years to come to an agreement, which combined with the time needed for migration/redevelopment makes me think that planning should start now. Regarding GDPR, the obstacle is the Court of Justice of the European Union's judgement in the Schrems II case, which rules out cloud solutions hosted by US owned companies. Links: https://www.eff.org/deeplinks/2020/07/eu-court-again-rules-nsa-spying-makes-us-companies-inadequate-privacy https://www.europarl.europa.eu/RegData/etudes/ATAG/2020/652073/EPRS_ATA(2020)652073_EN.pdf https://www.europarl.europa.eu/RegData/etudes/ATAG/2020/652073/EPRS_ATA(2020)652073_EN.pdf Our DPO has the final word, and there is a discussion right now on continued use of Office365 in our organiszation. So far, nobody has given any comments on batch processing in a Viya environment. Are we alone in the world with an automated metadata-driven solution, while everyone else sees job processing as a separate activity where dependencies and triggers are manually maintained in e.g. LSF?

ErikLund_Jensen · ‎02-17-2023

Hi @Quentin @SASKiwi @Al_CUA Thanks for your input so far, both comments and links. What we know We are forced to implement a new solution due to the retirement of SAS9, but we will in any case need SAS9 for years to come because the replacement of 6000+ production jobs deployed from DI Studio combined with building a scheduler to handle the daily batch will be a time-consuming process. SAS Data Integration Studio Replacement of SAS DI is a big issue. It seems that the SAS9 Compute Server in Viya can handle many of the challenges with conversion of ETL jobs, at least @Al_CUA to redeveloping everything in e.g. Python or Microsoft products. This is an important observation because we have a management that are open to other solutions beside SAS. SAS Enterprise Guide We have about 100 EG users. In my understanding SAS Studio will be the only programming interface to Viya, so EG will also be discontinued with SAS9. A lot of EG projects are used as production jobs in workflows, so they need to be converted. I assume that a conversion to SAS Studio is more or less straightforward, but anything else will be a challenge. SAS Information Delivery Portal. Most reports can be handled in SAS Visual Analytics or any other BI solution, but we have several which can be considered applications, as they include data collection, automatic selection of content to the current user etc. They are built by external SAS consultants, and we have no ideas about what to do with those, but we could always get somebody to develop web applications instead, and it would probably be a minor expense compared to the rest. SAS Visual Analytics We use SAS VA today in a separate server Linux cluster. I don't know if there is a full Viya platform there also, but in any case we don't use it as a programming platform today. There is nothing between the writing of data to CAS libnames from our SAS9 environment and the building of reports on these data, except for maintenance of CAS libnames. For other reasons, our management is looking into a possible replacement for SAS VA, but this might be irrelevant if we already have a Viya platform and ends up with choosing Viya as the future platform instead of SAS DI and SAS EG. Would anybody know if VA always comes with a full Viya that are open to use as a programming platform under the VA licence? Daily operation Job promotion, scheduling and batch execution is the remaining - and in my position the most important - issue. We don't know how to handle the concept of separate environments for development and production, controlled promotion and job scheduling in Viya (or any other product). We also don't know how a replacement for our current batch setup could be established. Is is - as described in a previous post - based on actual metedata content, so execution order is established based on dependencies extracted from metadata, so releasing, omitting or rerunning jobs in case of errors is fully automated. Would anybody have links to papers adressing batch operations in a Viya environment?

ErikLund_Jensen · ‎02-16-2023

@SASKiwi Thank you for this interesting input. We share your opinion that Viya has a very complex architecture, and our IT already struggles with Linux Grid, as it is basically a big Windows installation. I am afraid that cloud hosting isn't an option due to GDPR-rules, and I fear that in-house hosting will be a challence for our IT Shop, as it already struggles with Linux Grid, Kerberos etc. I also think that we need to continue with SAS9 as long as possible. It will take years to rebuild out actual batchjobs, whether they should be converted to Viya or something else, so the sheer size of the task forces us to choose a stepwise migration. Besides, the user organisation will also need a long time span do a stepwise migration from SAS Enterprise Guide to SAS Studio or whatever. I had hoped for responses from people that are not only planning a migration, but in the process, so they had some answers regarding conversion time and a new way of handling promotion/scheduling/batch, so I wonder if I have choosen a wrong forum for this post. It would also be interesting to get some comments from SAS representatives. It is possible that nobody read my post, but it is worrying to think that the silence is caused by a lack of good answers.

ErikLund_Jensen · ‎02-13-2023

Hi community As we are approaching the retirement date for SAS9, the time has come to start planning a migration to Viya. We don't know much about Viya, but we see three bigger obstacles that I want to discuss with the community, and I hope this discussion will ease our minds, though I fear the worst. SAS Information Delivery Portal SAS9M8 is available now with support expected until 2028, so we thought that we had 5 years left to find out what to do. But it is a nasty surprise to see that the Information Delivery Portal is retired with this release. M7 is supported until 2025, so we have a couple of years to find a replacement for our current reports. They are all built with use of javascript to provide selectboxes to control the content of displayed tables and graphs, and we use a framework supplied by SAS to generate the scripts. It works with special DI Studio transformations that generate the javascript and write it to the web server, but I am not sure if this framework is "official" SAS or something developed in our local SAS company. So I wonder if or how a similar functionality could be obtained in SAS Visual analytics. Converting SAS programs to run under SAS Viya There is a lot of information available on this topic, but it seems that converting even a simple job will be a time-consuming task, and we have many jobs doing things I never saw addressed, like interacting with Linus to use character conversion routines, retrieve folder content and other information from Linux, interact with LSF Process Manager, get content from FTP servers, run bash scripts, using macro loops, fcmp-functions etc. So how are these non-standard tasks implemented in Viya, and what to do if they are not? We have about 6.100 jobs with at least 30.000 transformations built in SAS Data integration Studio and besides about 200 SAS programs executed by CRON/bash scripts, and we have no idea about the total time that should be allocated to the whole conversion. Deployment and Scheduling We run a SAS Data Warehouse with more more than 700 LSF Flows containing more than 6.000 scheduled jobs in the daily batch. At least 600 of the flows depend on previous flows, and a dependence chain can be up to 20 flows long. The scheduling works so any flow is held until all previous flows on the daily run list are finished, and then be either triggered to be run by LSF or omitted if a previous flow is failed. This is the basics, there is other funcionality included. We deploy the jobs from SAS Data Integration Studio, where jobs are organized so one folder with jobs corresponds to one flow. Flows are created and jobs added in SAS Management Console, and they are scheduled to LSF as "Manually" without any triggers or depencencies defined. It is not necessary, because our home-built scheduler (SAS code) does the rest. It determines the flow dependencies by extracting information from SAS Metadata, and it monitors job and flow results and triggers or excludes flows depending on previous results. Everything is written to a control database, and a web application is used to follow the current batch status. We have used this scheduler for 12 years, and it works with minimal intervention, so it takes a few hours per month to keep the the whole batch up to date and running, including rerunning of flows after errors. Now we try to figure out how we can get a similar functionality in SAS Viya. It seems that Viya is not built with a batch environment in mind, and from what I have read I gather that it is a long and complicated process to deploy a SAS Studio job as program code and create a script to execute it, and then what? It seems that the concept "Flow" and functionality comparable to LSF is missing in Viys, no scheduler to run the scripts with respect for dynamically derived dependencies, and no metadata to extract dependency information from. We are not alone in the world with a setup that depends on traditional batch processing, so somebody must have a setup to handle that in viya. The question is how time-consuming the tasks with defining scheduling details, running and rerunning jobs, maintaining of correct execution order, monitoring batch progress etc. is, i.e. how many extra hands will be needed in the daily work. When it comes to development, we use about 3 minutes to deploy a new/changed job from DI Studio and add it to a flow. We suspect the process will be a much more complicated and time-consuming process in Viya, and it might require a lot of additional work to set the scheduling up too, depending on the solution to we find to handle that. We have at least 50 new/changed/obsolete jobs in at least 10 flows per week, often several times more, and we have no idea about the extra workload we can expect. Today, We are 5 persons in a team to run a Linux cluster + metadata servers and web servers, get data from external sources, manage authorizations, manage client program, promote jobs to production, schedule jobs, monitor the daily batch, handle odd tasks and help users. We support 25 ETL developers and more than 100 EG Users. We cannot expect to cope with this without extra help. A team of extra persons is probably needed for the migration process, but we fear that we also will need several extra persons to handle the daily routine afterwards with a decent level of quality and quick response. We need an estimate, so we can prepare our management, but more than anything we need a basic understanding of how we are going to do things in the future, down to tools and techniques. Our SAS consultants has a tendency to become uneasy and vague when these questions come up, so I look forward to any comments, thank you.

Online Status	Offline
Date Last Visited	‎06-24-2025 05:37 AM

Re: SAS Metadata: list of all attributes you can use in METADATA_GETAT...

Re: SAS Metadata: list of all attributes you can use in METADATA_GETAT...

Re: Split name vertically

Re: How to flag the last AVALC = 'Y' record prior to this AVALC='N' re...

Re: The use of last.id by multiple groups

Re: combine datasets even when results are missing

Re: combine datasets even when results are missing

Re: How do i parse specific number combinations from a field in a tabl...

Re: How do i parse specific number combinations from a field in a tabl...

Re: Pseudonymization of sensitive data

Re: How to convert DATETIME to ISO8601 format ?

Re: create macro vars-End of month

Re: Flag the next value of a variable

Re: SAS Programming 1_Lesson 5_p105a03.sas: repeating the same code do...

Re: Write SAS Time fields to Excel - loses time format

Re: combine datasets even when results are missing

Re: Pseudonymization of sensitive data

Re: SAS Connect To ODBC with Big Query issue

Re: SAS code for assigning different values of race for an individual ...

Re: Unable to open a particular job in SAS DI Studio

Re: Macro Question

Re: Count the number of occurrences of any items in a list

Re: Does the start= option inside of a data step actually exist?

Re: Does the start= option inside of a data step actually exist?

Re: How To Import Multiple Excel Files Into SAS As Separate Tables

Re: How to search for a list of strings across files (.sas) in a libra...

Re: Filtering a dataset on an indexed date field takes long

Re: SAS DI: How can I extract the code that finds and generates source...

Re: How to open a sas7bdat.gz file?

Re: How to concatenate different rows?

Re: Help formatting date variables YYMMDD and M/D/YYYY

Re: Discussion of transition from SAS9 to Viya. Call for input.

Re: Discussion of transition from SAS9 to Viya. Call for input.

Re: Discussion of transition from SAS9 to Viya. Call for input.

Discussion of transition from SAS9 to Viya. Call for input.

SAS Inner Circle Panel

SAS Analytics Explorers