FAQ Database Discussion Community


SAS how to get random selection by group randomly split into multiple groups

sql,sas,sample,proc
I have a simple data set of customers (about 40,000k) It looks like: customerid, group, other_variable a,blue,y b,blue,x c,blue,z d,green,y e,green,d f,green,r g,green,e I want to randomly select for each group, Y amounts of customers (along with their other variable(s). The catch is, i want to have two random selections...

sas create a variable that is equal to obs column

sas
I have a file with 10 obs. and different parameters. I need to add to my data a new variable of 'ID' for each observation- i.e a column of numbers 1-10. How can I add a variable that is simply equal to the obs column? I thought about doing it...

Getting started with SAS parallel processing using MP Connect

sas
I am trying to understand MPConnect and how I can use it for parallel processing. As a simple example, I started a session which prints "Hello World!" forever and another which prints "Bye World!". I said "waitfor any" and "rget" and I expect "Bye World!" in the Log because "Hello...

SAS Macro and arrays for tables names

sas,sas-macro
i am new into SAS, i am looking at this code: libname FINAL 'C:\PERM'; %LET PERM_DB = C:\PERM_DB.ACCDB; %LET PASS = 1234; %LET EXTRA1 = _A; %MACRO PERM(tablename); PROC IMPORT OUT= FINAL.&tablename&EXTRA1 DATATABLE= "&tableACCESS&EXTRA1" DBMS=ACCESS REPLACE; DATABASE="&PERM_DB"; DBPWD="&PASS"; SCANMEMO=YES; USEDATE=NO; SCANTIME=YES; RUN; %MEND PERM; %PERM(table1); %PERM(table2); %PERM(table3); %PERM(table4); %PERM(table5); %PERM(table6);...

SAS: Creating Tables Dynamically for different dates and table names in one go

sas,sas-macro,enterprise-guide
I'm stuck to something about creating tables dynamically by date filter. I have SAS code for setting date filters before running main codes. This it the date code; data _null_; /*ACTUAL DATES*/ R_act_beg=intnx('month',today(),-12,'beginning'); call symput('R_act_beg',R_act_beg); R_act_end=intnx('month',today(),-1,'end'); call symput('R_act_end',R_act_end); name_m=put(month(intnx('month',today(),-1)),z2.); call symput('name_m',name_m); name_y_act=put(year(intnx('month',today(),-1)),z4.); call symput('name_y_act',name_y_act);...

What is the SAS format and informat for “mm/dd/yyyy hh:mm:ss”?

datetime,format,sas,informat
The Question Please is there a built-in SAS format and informat for datetime in mm/dd/yyyy hh:mm:ss? For example: 06/25/2015 03:02:01 (June 25th 2015, 3 o'clock 2 minute 1 second). The Background I am trying to parse a CSV flatfile to a SAS dataset in which a datetime column is currently...

An efficient way to Copying values in subsequent records - SAS

sas
I have a dataset that is grouped by category variables in the source data. For example: Bar | Foo1 | Foo2 | Foo3 Bar2 | Foo4 | Foo5 | Foo6 After I import the source data, the above would result in the first Variable (Parent) being populated on Record 1...

How to publish a (csv, xlsx, etc) file from SAS DIS via email

email,sas,sas-dis
So I have some SAS DIS jobs which create "kickout" data when run - by this I mean that if things run smoothly, none of the "kickout" data is generated, but it is known that there will be exceptions and I would like to have those exceptions put into a...

Use code in a file as content of a SAS macro variable

file,import,sas
I have a series of files, each containing SQL code. I'd like to use SAS to run passthrough queries using that SQL. Since those files are changing often I just want an automated way to keep SAS synched up with these files. I'm thinking that if I could import the...

What's wrong with these macro parameters?

sas,sas-macro
I have the following simplified version of a piece of code that I am working on: %macro test(var); %if &var = 'Sub Prime' %then %do; %let var2 = 'Sub_Prime'; %put &var2; %end; %mend; %test(Sub%str( )Prime); Basically the point of this is that if var = 'Sub Prime' that var2 should...

how to change values of a column in sas dataset

sas
my dataset is like this I want to change all 'Within the next 6 months' to '< 6 Months' Similarly all of the 'Between 6 months and a year' to '6 to 12 months' How can we do that? ...

SAS Concatenate Multiple Variables to Create Data-Driven Macro Statements

sql,macros,sas,append,concatenation
In order to keep my process data-driven, I'm trying to concatenate multiple variables, separated by comma, in order to ultimately put them in a PROC SQL list to call in multiple macro statements that would otherwise clutter my SAS pogram. Take the following sample dataset: DATA TEST; INPUT YEAR CONDITION...

how to use _infile_ in SAS to delete lines in a hierarchical prog

sas,delete-row
Trying a hierarchical prg in SAS Studio I'm trying to retain the Buyer code and Vendor with a retain statement as "headers" and read in the detail with a if/then conditional statements, but cannot figure out a way to do this with the repeating labels in the way. How can...

check if a number is a positive integer

sas
Is there a pre-defined function for checking if a number is a positive integer? e.g 2.17 -> NO 3 -> YES Currently I'm doing this: if number - int(number) = 0 then 'YES' else 'NO'...

changing the order of values in a variable in sas data (row-wise ordering )

sas
My sasdata output is like this. Can we make the values in a particular order ?(change the order of rows) ie In the order of "less than 10 lakh, between 10-20 lakh,between 21-30.... above 1 crore". I want to change the row order. Right now rows are ordered in ascending...

Using R1C1 formula in VBSCRIPT

excel,vbscript,sas
I create a excel document with SAS using VBSCRIPT. Here is the code that work : filename vbscript "&fich_trim..vbs"; DATA _NULL_; FILE vbscript; PUT "Dim Exc, Classeur"; put "Dim Col"; PUT "Set Exc = CreateObject(""Excel.Application"")"; PUT "Exc.Visible = False"; PUT "Set Classeur = Exc.Workbooks.Open (""&FICH_trim..XML"")"; do i = 4 to...

split a dataset into N pieces

sas
N is determined by the number of distinct values in particular fields. In actual scenario, N is quite large since each field (char column) has many distinct values. Sample Data: A B C D V1 V2 V3 V4 ab ba cf dm ab bc cf dm ab bc cg dm...

Which is faster, where statement or where data set option

sas
The question is really straight forward, which one is faster? Considering we are using data step with two datasets in the set statement and the datasets have the same variables in them. From What Ive heard and read, if we subset them using the same condition, say date = "10jan2014"d,...

SAS do loops and macros

sas
I tried to develop the code and make sure that I will include the comparison with the newly extended average, however it does not work the way it should be. can you please advise what needs to be done so that condition (value1 < saved_average and value2 < saved_average and...

Scheduled .bat file running SAS script fails to assign library but works when manually run?

batch-file,sas,scheduled-tasks
I have a scheduled task which is supposed to run a SAS script daily. It's unsuccessful, however, and leads to a result of 0x2. The .bat file is simply: "E:\Program Files\SAS\SASFoundation\9.2\Sas.exe" -sysin "C:\Documents and Settings\username\Desktop\myfolder\myscript.sas" I have logged the output and it indicates that the problem is that a library...

Accumulated Monthly Percentile Calculation in SAS PROC SQL

sql,sas,proc-sql
I have a table that closely resembles the following format: ID | Month_ID | Param1 | Param2 1 | 1 | 5 | 10 1 | 1 | 6 | 12 1 | 2 | 4 | 9 1 | 2 | 8 | 15 2 | 1 | 3...

Regex with whitespaces and preceding zeros

regex,sas
I want to match the string 11 with a regular Expression in SAS. The 11 can be preceded by zero or more 0 and/or by white spaces. Any other character is not allowed. Likewise, if anything there should only be white spaces following the 11. Examples: Match: 0000011 11 11<space><space>...

Importing single value from a CSV file not working in SAS

sas
I'm trying to use a Macro that retrieves a single value from a CSV file. I've written a MACRO that works perfectly fine if there is only 1 CSV file, but does not deliver the expected results when I have to run it against more than one file. If there...

SAS - sub select with like

sql,sas,proc-sql
hope everyone had a good weekend. I have a table (tableA) with one column that has values such as the below: Column1: xyz-12345678 rdr-32343234 fgm-23423423 Then i have another table (tableB) with one column that has all the values in tableA.column1 and then some more such as the below column1:...

Reading next k observation from current observation

sas
Here's a very similar question My question is a bit different from the one in the above link. Background I have a data set contains hourly data. So each object has 24 records per day. Now I want to create K new columns represents next 1,2,...K hourly records for each...

Probt in sas for column of values

sas
Im looking do a probt for a column of values in sas not just one and to give two tailed p values. I have the following code Id like to amend data all_ssr; x=.551447; df=25; p=(1-probt(abs(x),df))*2; put p=; run; however I would like x to be a column of values...

sas conditional sum into new field

sql,sas,conditional
I'm new to SAS and have a simple dataset called ORIG_DATA from which I need to create a new dataset SUMMARY which shows the total by Salesman_ID by Day_ID In essence the SUMMARY output should look like this where the numbers are the sum of totals. Salesman_ID|Day_1|Day_2 A |30 |40...

sas: proc sql select into with more than one output

sas,select-into
I have following dataset data height; input name $ var $ value; datalines; John test1 175 Peter test1 180 Chris test1 140 John test2 178 Peter test2 182 Chris test2 148 ; run; I would like to make mean value of 2 tests for each students I able to make...

how to calculate weighted average but exclude the object itself using SAS

sas
There are four variables in my dataset. Company shows the company's name. Return is the return of Company at day Date. Weight is the weight of this company in the market. I want to keep all variables in the original file, and create an additional variable which is the market...

Filter a table by column name in sas enterprise guide

sas,enterprise-guide
How to use a column as a filter for a table in sas enterprise guide? Example: Got a column consisting of personal IDs and want that column as a filter on my table with information about ALOT of people. Hope you can help me....

Insufficient authorisation to lst in SAS batch job

sas
Today I faced a problem and solved it, but I am not quite sure why the problem occured. We have a SAS batch job: /path_to_script/sasbatch.sh -log /some_path/Logs/replication_#Y.#m.#d_#H.#M.#s.log -batch -noterminal -logparm "rollover=session" -sysin /another_path/macros/replication.sas And today the job fell over with error: ERROR: Insufficient authorization to access /sas_path/sasconfig/Lev1/SASApp/replication.lst. I found that...

Convert SAS date value to Java YYYY-MM-DD

java,date,datetime,sas
I have SAS date objects stored as integer and they look like : 19725. I am trying to write java code to convert the date to YYYY-MM-DD I see in the documentation that the SAS date value is the number of days from 01 Jan 1960 For example: 02 Jan...

Group by date using proc sql in SAS

group-by,sas,date-formatting,proc-sql
I want to sum up all values for the same cost centre per each year in SAS. I tried with Group By function in Proc SQL. PROC SQL CREATE TABLE incident_data AS SELECT Cost_Centre_Dim.Cost_Centre_Name, Sum(Count*Amount) AS TOT, OCC_DATE format= Year4. FROM Incidents, Incident_Type, Cost_Centre_Dim WHERE Incidents.Incident_Code = Incident_Type.Incident_Code AND Incidents.Cost_Centre_ID...

SAS sorts my macro variable values

sas,sas-macro
I'm trying to do some calculations based on different currencies where the output dataset consists of a column with company names and the following columns are the sum for each company in each specific currency (So one sum column per currency). I want to do the calculations through a loop...

product of common variables in two datasets

sas
data a1 a b c 2 3 4 1 2 3 data a2 a b d 0 .3 1 0 .2 0 proc sql; create table a3 as select a.*, a.a * b.a + a.b * b.b as Value from a1 a, a2 b; There are many common columns in...

Count number of 0 values

sas
Similar to here, I can count the number of missing observations: data dataset; input a b c; cards; 1 2 3 0 1 0 0 0 0 7 6 . . 3 0 0 0 . ; run; proc means data=dataset NMISS N; run; But how can I also count...

merging all columns in sas dataset who has column “shiyas” in header

sas
I have a sas dataset with columns shiyas1,shiyas2,shiyas3 in it. That dataset has some other columns also. I want to combine all the columns with header with shiyas in it. We can't use cats(shiyas1,shiyas2,shiyas3) because similar datasets have columns upto shiyas10. As I am generating general sas code, we cannot...

sas macro: argument to be a word in filename [duplicate]

sas
This question already has an answer here: Why won't my macro variable resolve? 1 answer I have similar files in a specific folder. I need to run same program for every files. So I thought of using macro. But I encountered a problem. %macro xyz(cityname); *IMPORTING FILE; proc import...

create a macro in sas

macros,sas
I have a report that is generated once a year. each report has the form of the year inside the name - report-2011.xls, report-2012.xls etc. each report contains the following vars: ID, SAL=average monthly salary of that year, Gender (0=male, 1=female), Married (0=not married, 1=married), I need to create a...

Support results in association rules are less than 5%

sas,market-basket-analysis,enterprise-miner
I am facing an issue with Association rules. I have a dataset which consists of transaction ID and ProductID I have edited the variable and changed TransactionID role to "ID" and productID role to "Target" The minimum support % is set to 5%. But when i run the association i...

How to get rid of unwanted space in SAS

sas
I have a variable called trouble_maker. Some observations have more than hundred unnecessary space before it, such as__________abc. Can anyone tell me how to get rid of the space before the first character please.

How to get array size during compile time?

sas
I have a dataset holding parameters like thus Parameters year threshold1 threshold2 1 100 200 2 150 300 .... 7 200 390 I can do data output; set input; if 0 then set set parameters; array thresholds [2] thresholds:; %do year = 1 %to 7; year = &year.; set parameters...

PROC SQL Update Efficiency for Large Datasets

sql,performance,sas,large-data
I have a SAS Master Dataset with 10 Million Rows and 1800 columns. I need to update 10 columns using a transaction dataset with 15 million records, only for records with matching key. I tried running a proc sql update statement using the following code. proc sql; UPDATE lib1.master1 a...

How to sort variables horizontally with arrays and use of call sortc

sas
A user on sasprofessionals.net had a problem of not being able to group his dataset by several variables where variable values are interchangeable within the observation because they carried the same meaning. In the example dataset, observation 2,3 and 7 are the same because each of them have A14, A14...

Extract ID's separated by dashes from text string

sas,delimiter
I have a dataset that has one concatenated text field. I am trying to break it into three text columns in SAS 9.4. Obs Var1 1 MAY12-KANSAS-ABCD6194-XY7199-BRULE 2 JAN32-OHIO-BZ5752-GARY My output for observation 1 should look like this: Obs Date State ID 1 MAY12 KANSAS ABCD6194-XY7199-BRULE Here's what I have,...

How to change the column headers of a sas dataset into an observation?

sas
I have created a sas code which generates many sas datasets. Now I want to append all of them to a single excel file . So first I want to convert all the column headers of sas datasets as first observation. Then leave space between these datasets (adding a blank...

SAS proc sql - how to read in log of variable but retain the variable's label

sql,sas,label
I am reading in variables from a few different datasets using proc sql, which I am trying to improve on. What I'd like to do is read in a variable from a dataset using proc sql, but take the log of the variable as it's read in, but keep the...

Get the ith word in a macro variable list

macros,sas
%let TableList = TableA TableH TableB TableG; Words in &TableList are separated by ' '. How can I retrieve certain word to do the following? I do not know the number of words in the tablelist and would like to get the nth word from the list. Given i =...

randomly select two observation and calculate the distance

sas
I have a data set have with numerical column x. I want to randomly select any two distinct points and then calculate the distance between them. If I only do it once, then I just use proc surveyselect to generate another data set with two obs. proc surveyselect data=have out=want...

SAS Proc SQL how to perform procedure only on N rows of a big table

sas,proc-sql
I need to perform a procedure on a small set (e.g. 100 rows) of a very big table just to test the syntax and output. I have been running the following code for a while and it's still running. I wonder if it is doing something else. Or what is...

SAS: Reading Date field with one informat which it is saved in a different date format

sas
I was trying to read a date field using date. informat from a SAS dataset. This dataset was created using a date format of mmddyy10. I thought I will get incorrect results when I realized that I was trying to read the date with incorrect format than what it was...

SAS: conditional statement error?

sas
Could you please help me understand why this statement is incorrect (from a quiz). For some reason I can't see a problem. if total = 140 then status EQ 'works'; Thanks!...

SAS Gplot overlay line plots

plot,sas
I am trying to plot two sets of line graphs on the same chart: /* make data */ data test ; do i = 1 to 2 ; do x = 1 to 5 ; y = i*x ; z = 0.5*i*x ; output; end ; end ; run ;...

SAS drop multiple variables indexed by tens

optimization,sas
My question is likely stupid but I have not found an answer yet. I have a variable var index by tens : var10, var20... var90. At some point of my code I want to drop all of them. I can do data want(drop=var10 var20 var30 var40 var50 var60 var70 var80...

Using SAS to check if columns have specified characteristics

arrays,data,sas
I have a dataset that looks like the one below. each row is a different observation that has anywhere from 1 to x values (in this case x=3). I want to create a dataset that contains the original info, but four additional columns (for the four values of Bin present...

SAS: getting the filesize of created DBF file

stored-procedures,sas,sas-macro
I have SAS stored process that ceates DBF file from SAS data set rr_udf_value and finds its size (F_SIZE): filename dbfout "/SASInside/DBF/myfile"; proc export data=rr_udf_value outfile=dbfout dbms=dbf replace; run; %let f_nm=/SASInside/DBF/myfile.DBF; %let rc=%sysfunc(filename(onefile, &f_nm.)); %let fid=%sysfunc(fopen(&onefile)); %let F_SIZE=%sysfunc(finfo(&fid,File Size (bytes))); %put &F_SIZE; The problem is that the variable F_SIZE is...

Match last values of different variables from a table

sql,sas,proc-sql
I have a table storing values of A and B for different dates. It looks like this: Date Amount Type 2015 15 A 2014 -3 B 2013 8 B 2013 10 A 2012 5 A etc. How to most effectively prepare a table that lists current A and current B...

SAS: Data step is behaving strangely

sas
I am doing practice assignment as part of my BASE SAS certification prep, to see when a data step ends. Below is the code: data first; input x; datalines; 1 2 9 ; run; data second; input x; datalines; 3 4 5 6 ; run; data third; set first; output;...

Detecting the latest modified date in a folder using SAS

sas
I am writing a program that needs to run every day. In a specific section of the program, all datasets in a specific library will be appended into one dataset in another library. My plan is to scan the first library to retrieve the latest modified time and then compare...

SAS Macro to Combine Municipal Proc SQL Statements Based on Date Criteria

date,sas,sas-macro
I have a series of proc sql statements which pull data for Active, Inactive and Lapsed customers. I end up with 3 tables. *Customers_Active *Customers_InActive *Customers_Lapsed Active: 0-12M purchaser Inactive: 13-24M purchaser, did not purchase 0-12M (active day range minus 12 months) Lapsed: 25-36M purchaser, did not purchase 0-24M (inactive...

SAS: Looping over column names

sas
I have a data set with the following structure: data account; input Index c1 c2 c3 c4 c5 c6 ; datalines; 4 30 20 10 30 40 20 3 50 20 30 50 10 20 ; run; In my file, there are 150+ columns of the "c"-Type containing numbers. In...

Plotting 3d graphs in SAS University Edition

sas
I'm using SAS University edition and I'm trying to find a way to output 3d graphs. I know about the procedures: g3d g3grid However they are not available in my SAS edition, and I was looking for something similar with no avail....

How to find percentage values of a column in sas?

sas
Friends, In SAS how can we find percentage values of a column? My data set output and its code is given below and the code is data out.calculate_age; set out.calculate_age ; if age = "" then age = "All"; if d=. then d=0; if s=. then s=0; if i=. then...

Duplicates issue

duplicates,sas
I have a problem with duplicates. Actually what I need is only the see duplicates but my table has many variables something like the below: a b c d e 32 ayi dam som kem 32 ayi dam som tws 32 ayi dam tsm tws 12 mm ds de ko...

SAS proc sql with when then statements

sas
I have a "time" var of years in my data. I need to create a new var based on the following with PROC SQL if time>mean(time)then new var=1 else, new var=0 I keep getting different error, how can I improve my code? proc sql; create table v3 as select*,case when...

Search/replace with special characters in macro variable

regex,sas
%let st1 = a b c; I would like to do a search/replace to get a new macro variable like this %let st2 = a_b_c; or %let st3 = a%b%c; or %let st3 = a!!b!!c; Basically just replace certain char with _ or % or !!. I think prxchange is...

SAS proc ttest modify HO

sas
I have a data with GENDER=(1/0) INCOME SENIORITY=(1/0). I need to run a ttest on INCOME by GENDER for SENIORITY=1. As far as I know, the default of HO=0, which means that there is no difference between the genders, but how can I define an HO that will check if...

long text file to SAS Dataset

import,sas,large-files
I am trying to load a large text file(report) as a single cell in SAS dataset, but because of multiple spaces and formatting the data is getting split into multiple cells. Data l1.MD; infile 'E:\Sasfile\f1.txt' truncover; input text $char50. @; run; I have 50 such files to upload so keeping...

Standardising dataset attributes across projects

sas,metadata
Background: I have multiple old projects that I need to standardise (PRJ01-PRJ10). Each is stored under its own libname and each has around 30 datasets (note: not all studies have the same 30 datasets). The variable names have remained consistent across the projects. However, over the years, the labels and...

Which one is default ODS destination in SAS - Listing or HTML?

sas,ods
Which one is default ODS destination in SAS - Listing or HTML? In SAS BASE Prep book, it says Listing but in Step by Step SAS programming it says HTML.

How to Declare Global Array Variable in SAS?

arrays,sas,global-variables,sas-macro
I'm new to SAS and spinning my wheels. The SAS documentation and other Google searches have not helped me figure this out. How can I declare a global array variable that I can use in various procedures to loop through the contents? Here is what I've tried: %let fileArray =...

Get rid of kth smallest and largest values of a dataset in SAS

dataset,sas
I have a datset sort of like this obs| foo | bar | more 1 | 111 | 11 | 9 2 | 9 | 2 | 2 ........ I need to throw out the 4 largest and 4 smallest of foo (later then I would do a similar thing...

Using for loop indices in variable generation SAS

for-loop,sas
I would like to set up a for loop in SAS where I would like to create time dependent tables. The idea is rather simple. I have multiple tables where I would like to left join them and i would like to this operation for every month. I dont have...

How to reference two dataset in SAS

sas
I have two separate datasets Data1 and Data2. Data1 contains a set of names. Data2 contains names that have changed (original and new name). How can I use Data2 to update Data1 with the new names? Data1: Richard, Tommy, Apple, John, Data2: (original:first column) Richard; (current: second column) Big Richard...

select maximum value of common column for multiple data set

sql,sas
I have a daily schedule process flow which refreshes a bunch of tables within the same library. At the end of the process flow, all tables should have the same up to date records. And I want to double check this via checking the maximum value of date. But problem...

In my sas program i only want data down from my mainframe server where date=20150427

sas,where
I want to ask my mainframe server if i only could get the data from the date=20150427. Like a where statement in SQL So problem is that the data is "flat" data so i have defined each column with a input statement i tried to use where=(L7DANDAT=20150427) L7DANDAT is the...

removing common prefix or suffix

sas
I have a data set contains a series variables named; PG_86xt, AG_86xt,... with same suffix _86xt. How can I remove such suffix while renaming these variables? I know how to add prefix or suffix. But the logic of removing them seems to be a little bit different. I think proc...

Replicating random normal generated in SAS (rancor) in R, based on the same seed?

r,sas,random-sample
Given the same seed, is there a way to produce the exact same random normal numbers generated in SAS, using the rannor function, in R?

SAS: Difference between current_date and today funtions

sas
I was wondering why there are two functions in SAS to achieve the same result. Current_date and today(). Could someone tell me if there is any difference between two.

how to use nobs in sas so that it can be used to find percentage frequency

sas
I have imported one datafile. It has 246 observations. By using nobs how can I replace 246 in the last sentence of my code to find percentage? proc import datafile='G:\Data file\Dec 2014.csv' out=out.datafile dbms=csv replace; *SUMMARY TABLE IS MADE; proc summary data = out.datafile missing; class AGE sentiment; *age, sentiment...

SAS array define error

arrays,sas
%let ng = 4; data a1; set a2; array cur{&ng} cur1-cur&ng.; do i = 1 to &ng.; if (_n_ = (i-1)*5 + 1) then cur[i] = Val; end; run; Error msg ERROR: Missing numeric suffix on a numbered variable list (cur1-cur). ERROR: Too few variables defined for the dimension(s) specified...

SAS: How can I filter for (multiple) entries which are closest to the last day of month (for each month)

loops,date,filter,sas
I have a large Dataset and want to filter it for all rows with date entry closest to the last day of the month, for each month. So there could be multiple entries for the day closest to the last day of month. So for instance: original Dataset date price...

Sas .dat file without column headings only 1st row being read in sas studio

sas
I'm using Sas studio university edition. I have a dat file without any column headings (4 columns). I'm trying to read it in with data van; infile "/folders/myfolders/test2/psek-win.dat"; input a $ b $ c $ d $; run; i.e. create my own names for the columns. It works, but only...

How to match data in SAS

sas
I have a dataset which contain three variables var1, var2, and Price. Price is the price of var2. var1 is a subsample of of Var2. Now, I want to find the price of each product in var1 by matching the name of Var1 with Var2. The data looks like this....

Email ID's from a SAS Dataset in Body

email,sas
I'm trying to send out ID's of a SAS dataset in an email but not able to get the format right. I just need plain text as html is getting stuck and slow. Thanks in advance to help! Any one solution would be good. I have one ID column. The...

SAS MACRO: Create many datasets -modify them - combine them into one within one MACRO without need to ouput multiple datsets

sas,sas-macro
My initial Dataset has 14000 STID variable with 10^5 observation for each. I would like to make some procedures BY each stid, output the modification into data by STID and then set all STID together under each other into one big dataset WITHOUT a need to output all temporary STID-datsets....

Dynamically create define in a PROC REPORT

sas
I have a dataset(liste_institution) that contain all the name of the variable that I want to "define" in my proc report statement. Here is my code that work when I call my macro not dynamically(%create_institution(815);). If I use the data statement with the call execute(in comment in my code) it...

Loop over strings and read in files and save [duplicate]

string,loops,import,sas
This question already has an answer here: Append multiple CSV files in SAS 5 answers I am pretty new to SAS. I have a bunch of files that are in csv format that I want to read into SAS. I need to read them in one-by-one and save them,...

SAS: assign a quantile to a macro variable

sas,sas-macro
In SAS, how can I assign the 97.5% quantile of the normal distribution to the macro variable z? Not working 1 %let z = quantile("normal", 0.975); Not working 2 %let z = %sysfunc(quantile("normal", 0.975)); ...

How to change my SAS code to find the maximum number

sas
I have a dataset looks like this. I want to create another variable which represent the total trading volume each day. My code shown below. But it seems that there is something wrong with my code, the calculated maximum trading volume N is wrong somehow. Can anyone tell me know...

Convert character string to date format

sas
Im trying to convert a character string of $40. to date format. below is the column Month in the dataset test2 and its values: Month Apr 15 May 15 Jun 15 I have tried this code but not getting the result I'm expecting. data test; set test2; Month =inPUT(month,monyy5.); /*...

Force SAS to continuously print results

sas,enterprise-guide
Suppose I have 3 proc tabulates in SAS. SAS will only display the results when all 3 finish. Is it possible to force SAS to display each table as soon as they are completed?

How to convert characters to date in SAS proc sql

date,sas,character
I have a column of data named yearmonth stored as characters data. I want to convert this column into SAS date in another column with format dd/mm/yyyy. for example lets say one of the data is 201201. I want to convert it into 15/01/2012. The problem is I can only...

How do I calculate the mode of a string variable within a group in SAS?

sas,proc-sql
I can calculate the mode using a subquery in proc sql, but is this the simplest way to do this? This code handles ties that might occur when calculating the mode by relying on however the max function in proc sql breaks ties. ods html file = "sas_output.html"; data raw_data;...

SAS else if clause confusion

if-statement,sas
I'm running the following code: data new; set old; if visits=. then band='Poor'; else if visits=1 or visits=2 then band='Low'; else band='High'; run; My confusion is when the else if statement is changed to: else if visits=1 or 2 then band='Low'; Why does the value Low appear as the band...

macro error: A character operand was found in the %EVAL function or %IF condition

macros,sas,sas-macro
%macro test(k); %if &k le 0 %then %put < 0 ; %else %put > 0; %mend test; %test(-5); %test(3.1); But %test(-3.1); will generate error ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &k le 0 I...

sas dynamic call symput with unknow number of fields in the dataset

sas
i have following dataset data parm2; input a b c d e; datalines; 1 2 3 4 A ; run; Problem1: I would like have a set of macro variables. Assume i do not know the number of fields and its corresponding name of the field. Problem2: fields are not...

SAS Find Top Combinations in Dataset

sas
Hell everyone -- I have some sales data which looks like this: data have; input order_id item $; cards; 1 A 1 B 2 A 2 C 3 B 4 A 4 B ; run; What I'm trying to find out is what are the most popular combinations of items...

How to count the number of same items on two different lists in SAS

sas
I am looking for the best way to count the number of executives shared by two firms. Currently, the data is arranged such that each row contains two firm IDs and a list of identifiers for each the board members of each firm. Currently, I have managed to obtain what...

calculate market weighted return using SAS

sas
I have four variables Name, Date, MarketCap and Return. Name is the company name. Date is the time stamp. MarketCap shows the size of the company. Return is its return at day Date. I want to create an additional variable MarketReturn which is the value weighted return of the market...